Deleting duplicate rows from a MySQL database

Esos shows a good way of removing duplicate entries from a database table. Of course, if primary keys were used this shouldn’t happen, but doing a select distinct into another table is neat !


You might also like

If you like this post then please subscribe to my full RSS feed. You can also click here to subscribe by email. There are also my fabulous photos to explore too!

Published by

Donncha

Donncha Ó Caoimh is a software developer at Automattic and WordPress plugin developer. He posts photos at In Photos and can also be found on Google+ and Twitter.

One thought on “Deleting duplicate rows from a MySQL database”


  1. Remove duplicate entries. Assume the following table and data.

    CREATE TABLE IF NOT EXISTS Test(
    pkey int(11) NOT NULL auto_increment,
    a int,
    b int,
    c int,
    timeEnter timestamp(14),
    PRIMARY KEY (pkey)

    );

    insert into Test(a,b,c) values (1,2,3),(1,2,3),
    (1,5,4),(1,6,4);

    mysql> select * from Test;
    select * from Test;
    +——+——+——+——+———————+
    | pkey | a | b | c | timeEnter |
    +——+——+——+——+———————+
    | 1 | 1 | 2 | 3 | 2004-04-16 10:55:35 |
    | 2 | 1 | 2 | 3 | 2004-04-16 10:55:35 |
    | 3 | 1 | 5 | 4 | 2004-04-16 10:55:35 |
    | 4 | 1 | 6 | 4 | 2004-04-16 10:55:35 |
    +——+——+——+——+———————+
    4 rows in set (0.00 sec)

    mysql>

    Note, the first two rows contains duplicates in columns a and b. It contains
    other duplicates; but, leaves the other duplicates alone.

    mysql> ALTER IGNORE TestdupTest ADD UNIQUE INDEX(a,b);

    mysql> select * from Test;
    select * from dupTest;
    +——+——+——+——+———————+
    | pkey | a | b | c | timeEnter |
    +——+——+——+——+———————+
    | 1 | 1 | 2 | 3 | 2004-04-16 11:11:42 |
    | 3 | 1 | 5 | 4 | 2004-04-16 11:11:42 |
    | 4 | 1 | 6 | 4 | 2004-04-16 11:11:42 |
    +——+——+——+——+———————+
    3 rows in set (0.00 sec)

Leave a Reply