Delete Duplicate records with exact value

user_18179703 0
Viewed 29

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);
duplicates mysql
2 Answers

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

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

user_180888220
Related