Delete all Duplicate Rows except for One record in MySQL

NB – You need to do this first on a test copy of your table!

When I did it, I found that unless I also included AND n1.id <> n2.id, it deleted every row in the table.

1) If you want to keep the row with the lowest id value:

DELETE n1 FROM names n1, names n2 WHERE n1.id > n2.id AND n1.name = n2.name

2) If you want to keep the row with the highest id value:

DELETE n1 FROM names n1, names n2 WHERE n1.id < n2.id AND n1.name = n2.name

I used this method in MySQL 5.1

Not sure about other versions.

Update: Since people Googling for removing duplicates end up here
Although the OP’s question is about DELETE, please be advised that using INSERT and DISTINCT is much faster. For a database with 8 million rows, the below query took 13 minutes, while using DELETE, it took more than 2 hours and yet didn’t complete.

INSERT INTO tempTableName(cellId,attributeId,entityRowId,value)
 SELECT DISTINCT cellId,attributeId,entityRowId,value
 FROM tableName;

Reference:

http://stackoverflow.com/questions/4685173/delete-all-duplicate-rows-except-for-one-in-mysql

Additional information

DELETE 
 t1 
FROM 
 tTable t1, tTable t2 
WHERE 
 t1.fieldName = t2.fieldName AND t1.id > t2.id

ELSE

create another table as below

CREATE TABLE myTable_new (ID INT PRIMARY KEY, Title varchar(20))

and add values as

INSERT INTO myTable_new (ID, Title) SELECT ID, DISTINCT Title FROM old_table

considering old_table is the earlier table…

source: http://stackoverflow.com/questions/9452320/remove-duplicate-records-except-one-record

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.