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!

Share

81 Responses to Remove duplicate entries / rows a mySQL database table

  1. jason says:

    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. John Ortega says:

    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. lawrence says:

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

    Not true….You can do this:

    CREATE TABLE NEWTABLE as
    SELECT * FROM OLDTABLE WHERE 1 GROUP BY column1,column2;

  4. Thyago says:

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

  5. dencio says:

    Quick and simple. TY

  6. […] 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. John says:

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

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

  9. sangeeta says:

    You can just do this:

    create table new_table1 (select distinct * from old_table);

  10. Stefan says:

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

    Cheers!

  11. […] 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/ […]

  12. Gauri says:

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

  13. qstraza says:

    ALTER IGNORE TABLE table_name ADD UNIQUE KEY(duplicate_field_name)

  14. vinoth says:

    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??

  15. sarath says:

    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

  16. sarath says:

    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. Chris says:

    Great man!

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

  19. Kuldeep says:

    It works great..got what I wanted.

  20. Andrew says:

    Thanks for sharing

    Was a painless procedure

  21. TNDesign says:

    Many THANKS for the big help 🙂

  22. Ondiege says:

    This helped a bunch. Thanks!

  23. Howard says:

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

  24. emma says:

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

  25. 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.

  26. this will prevent more duplicate in future

    ALTER IGNORE TABLE employee ADD UNIQUE KEY(employee_id)

  27. […] 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