Delete Duplicate records with exact value

February 14, 2022

How to delete duplicate records when both rows the exact same records?

create table item
(id int,
product_name varchar(20),
quantity int,
amount decimal(10,2)
);

insert into item values(1,"Laptop",10,89.9),(1,"Laptop",10,89.9),(2,"Mixer",8,82),(4,"Fridge",9,100);

This is deleting all the records for duplicate id

DELETE FROM item
WHERE ID IN (SELECT id,row_num FROM (SELECT ID, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS row_num FROM item) t
WHERE row_num > 1);

>Solution :

I would actually suggest creating a temporary table, inserting the records sans duplicates, then renaming the temp table to the original table name.

CREATE TABLE item2 (
    id int,
    product_name varchar(20),
    quantity int,
    amount decimal(10,2)
);

INSERT INTO item2 (id, product_name, quantity, amount)
SELECT DISTINCT id, product_name, quantity, amount
FROM item;

DROP TABLE item;
ALTER TABLE item2 RENAME TO item;
DROP TABLE item2;