Delete Duplicate records with exact value-CodePudding

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);

CodePudding user response：

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;

CodePudding user response：

Create a duplicate table with a new primary key or unique key then get all the duplicate records select id from item where id =1 HAVING COUNT(*)>1 will get you all the duplicate records with new primary key or unique key

copy the primary key which you want to delete

delete from item where id IN (1,2,3,5..............)