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!

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

Loading Facebook Comments ...