Jun

16

MySQL Remove Duplicate Entries

Filed Under Code Snippets 

The need to remove duplicate entries from a MySQL database is fairly common. This post will demonstrate the simple and painless technique I use to perform the task in three easy steps, including making a backup of the original table. The technique assumes that no updating of duplicate records in your MySQL table needs to take place. You will also need Alter privileges on the database.

Step 1:

First, we are going to pull a set of distinct rows from `old_table` by grouping the rows on the field or fields that need to be unique. We can use the select statement to create a new table with the unique rows all in one command:

CREATE TABLE new_table 
  AS SELECT * FROM old_table 
  WHERE 1 
  GROUP BY field1, field2

Step 2:

Next, we are going to back up `old_table` by changing its name to `old_table_bak`:

RENAME TABLE old_table TO old_table_bak

Step 3:

And finally, we are going to restore `old_table` by renaming `new_table` to `old_table`:

RENAME TABLE new_table TO old_table

And that’s it. We now have a clean table, with no duplicate rows and a backup of our original table – all in three easy steps.

Comments

Leave a Reply