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

Comments

  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. "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;

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

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

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

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

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

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

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