MySQL: Remove duplicate entry from the databse keeping one record

February 14, 2011 | In: mysql, web development

Sometimes we written a code to enter some dynamic entry in the table but did not want to enter duplicate records for same id ( not primary field). and we found that we have entered many records in the table and not it is not possible to delete record manually.

For this we can run this code

CREATE TABLE temp_tbl LIKE tblNameInWhichDuplicateReocrds;
INSERT INTO temp_tbl SELECT tblNameInWhichDuplicateReocrds.* FROM tblNameInWhichDuplicateReocrds;
DELETE FROM tblNameInWhichDuplicateReocrds WHERE ID NOT IN (SELECT MIN(ID) FROM temp_tbl GROUP BY dulicateFieldName);
DROP TABLE temp_tbl;