Blog Entry 13: !! Clones !!
Finding and Removing Duplicates in mysql !
You have a table with duplicate rows – somehow a unique index didn’t get created and a bug has added duplicate records to your table. A pox upon that bug! Here’s an easy way to clean out that table quickly.
Good but not perfect Say you have a tables where the duplicate field is the “telefonnummber”, i.e 0049 4561387974 appears too many times. The query below will group all the duplicates into one row.
SELECT * FROM table WHERE 1 GROUP BY [duplicate column]; like this SELECT * FROM users GROUP BY telefonnummber;
Perfect and Good Create the new table first like this
CREATE new_table LIKE old_table
This will create a new table with the exact same structure, without the data. NOTE: It also doesn’t preserve the Indexes.
And then copy all the data(non-duplicate across) using a INSERT…SELECT statement
INSERT INTO new_table SELECT * FROM old_table GROUP BY “telefonnummber”
Remember “telefonnummber” is the field you want to remove duplicates by or on.
These are not the only two methods for doing this, there are so many, for example(My personal favourite, one liner)
CREATE TABLE new_table SELECT * FROM old_table group by “telefonnummber”
Does all the work, copies all the data, again indexes not preserved
And there you go, you have a new table with no duplicates. Disclaimer: Double check.
