SQL Code

Remove duplicate entries / rows a mySQL database table

This is an extremely quick and painless way to remove duplicate rows (tuples) from a MySQL database table. The best part of it is that it requires no programming or PHP coding whatsoever, it can all be done with three manual SQL queries! Note: this only works on MySQL 3.23 and above. But I have a hard time imagining anyone running an older version than that anyways!

So, there are 3 steps, and therefore 3 SQL statements:

Step 1: Move the non duplicates (unique tuples) into a temporary table

CREATE TABLE new_table as
SELECT * FROM old_table WHERE 1 GROUP BY [column to remove duplicates by];

Step 2: delete delete the old table
We no longer need the table with all the duplicate entries, so drop it!

DROP TABLE old_table;

Step 3: rename the new_table to the name of the old_table

RENAME TABLE new_table TO old_table;

And of course, don't forget to fix your buggy code to stop inserting duplicates!



  1. A safer version would be to rename the old table to a backup instead of dropping it.

    Step 2)
    RENAME TABLE old_table TO old_table_backup;

  2. Do you really see this as a valid way to remove duplicates? Why would you remove a table when you only want to remove duplicates?

  3. "Sid Debgupta said,
    This will not work for multiple column uniqueness"

    Not true….You can do this:

    SELECT * FROM OLDTABLE WHERE 1 GROUP BY column1,column2;

  4. AWESOME! Dude, ur awesome, seriously. This is the most brilliant solution for this i've ever seen. Thanks!

  5. Quick and simple. TY

  6. Blue Topaz Games: SQL finding duplicate records.

    […] want something simpilar and are not necessarily interested in what record might have better data. Follow these instructions here on creating a new table to get rid of duplicate rows Tags: SQL […]

  7. Also remember to set that column to unique to ensure there aren't any duplicates.

  8. plots in ujjain, property in ujjain

    Thanks This helped me eliminate like 4000 rows in a table!!! GREAT code!
    this is best delete query

  9. You can just do this:

    create table new_table1 (select distinct * from old_table);

  10. SQL statement to list duplicate contents « azwan.net

    […] source […]

  11. Thanks for this! I had to use it as the duplicate records in my DB were preventing me from altering a Primary Key


  12. Query Untuk Menghapus Data / Record Yang Sama (Duplikat) Di Dalam Suatu Tabel MySQL « ayo belajar sama – sama …

    […] Sesuai dengan judulnya, berikut adalah langkah – langkah yang bisa kalian pakai untuk menghapus data / record yang sama dalam suatu tabel di database MySQL hanya dengan satu Query saja. Pengetahuan ini akudapat dari http://www.justin-cook.com/wp/2006/12/12/remove-duplicate-entries-rows-a-mysql-database-table/ […]

  13. Thanks! worked like charm. Else we were thinking of writing a php script to calculate hash, group by etc etc…
    This is so simple!!

  14. ALTER IGNORE TABLE table_name ADD UNIQUE KEY(duplicate_field_name)

  15. hey,i need a help in php.
    i need to check for same email id in the database ,if that id is exists,i need to replace with new id .For example if i have record like
    vj@c.com,vj@c.com,vj@c.com…..i need to change 2nd and 3rd id into vj1@c.com and vj2@c.com….can u help me with the code??

  16. hello friends,help me on this
    Create table person(p_id varchar(10) unique,name_s varchar(20))
    I have created a table person and set p_id as unique.Now i want to delete the Unique for the P_id.. how can i do that in sql server… reply me asap

  17. hello friends,help me on this

    Create table person(p_id varchar(10) unique,
    name_s varchar(20))

    I have created a table person and set p_id as unique.
    Now i want to delete the Unique for the P_id..
    how can i do that in sql server… reply me asap

  18. Полезное, mySQL | Новосибирен

    […] отсюда:Remove duplicate entries / rows a mySQL database table Запрос MySql для вытаскивания всех повторяющихся записей: […]

  19. It works great..got what I wanted.

  20. Thanks for sharing

    Was a painless procedure

  21. GENIUS!!!

  22. Many THANKS for the big help 🙂

  23. This helped a bunch. Thanks!

  24. I experienced this problem when trying to set a column as my primary key. Your solution was perfect. Thank you very much.

  25. Thank you so much this has saved me hours of work and a massive headache!

  26. Jody Fitzpatrick

    Great Post, I was going about this is another direction, but it seems like your's is a much better way — I was using this to filter duplicate city, and state postal code information on over 200,00 cities some duplicates some not – it helped in no time – much quicker then the way I was using it. I was creating a virtual table and just checking but this is great! – thx mate. have a great one.

  27. Latest Mac Downloads

    this will prevent more duplicate in future

    ALTER IGNORE TABLE employee ADD UNIQUE KEY(employee_id)

  28. Blogvaria » Finding duplicate entries in MySQL

    […] you just wish to remove any duplicate entries then take a look at Justin Cook's article on the subject. Share and Enjoy: These icons link to social bookmarking sites where readers can […]

Leave a Reply

Your email address will not be published. Required fields are marked *

To prove you're a person (not a spam script), type the security word shown in the picture. Click on the picture to hear an audio file of the word.
Anti-spam image