Remove Duplicates from MySQL table

Ever had to remove duplicate entries in a table because some GENUIS thought that it would be good design to not put a unique index on a field or better yet was too lazy to put in the proper checking to make sure that you are not inserting duplicates ? Well here is something to correct that sloppy laziness…..There are four steps to get this resolved.

Step 1. Make a backup of the table you are about to change in the event everything breaks.

mysqldump --add-drop-table -h db01.example.net -u dbocodex -p dbwp | bzip2 -c > blog.bak.sql.bz2

Step 2. Move the non duplicates (unique tables) into a temporary table

CREATE TABLE new_table AS
SELECT * FROM old_table WHERE 1 GROUP BY [COLUMN TO remove duplicates BY];

Step 3. Delete the old table

DROP TABLE old_table;

Step 4. Rename the new_table to the name of the old_table

RENAME TABLE new_table TO old_table;