MySQL: How To Remove Duplicate Rows From A Table

Fast and simple way to remove duplicates rows from your MySQL tables. No messing around with programming language (php,.net,ruby,python, etc). Just pure SQL. Here’s how.

Removing Duplicate Rows From MySQL Tables

First of all, you will first need to know how you define uniqueness (which set of column(s) should be unique for all rows?) and then group them together in the query. The easiest way to find out if you really have duplicate rows is try creating a unique index for the table based on this/these column(s). If you get an error, you have duplicate rows.

1. This will create a new table with only the distinct (no more duplicates) records from the old table.

CREATE TABLE new_table AS SELECT * FROM old_table GROUP BY col1,col2,col2...

2. You can remove/drop the old_table and rename the new_table so it has the same name as the old_table.

DROP TABLE old_table;
RENAME new_table to old_table

3. Done!

*Potential Drawbacks
1. Index from old_table are not carried forward to new_table
2. had to manually add primary index and automatic_increment on id in new_table

Comments welcomed. If you find a better way of doing this, please let me know.