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 !

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