Deleting duplicate rows from a MySQL database

One Reply to “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)

   Loading...

   Reply