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 !
Categories
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 !
Donncha Ó Caoimh is a software developer at Automattic and WordPress plugin developer. He posts photos at In Photos and can also be found on Twitter.
View Archive →By continuing to use the site, you agree to the use of cookies. more information
The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.
1 reply on “Deleting duplicate rows from a MySQL database”
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)