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
- MySQL Optimization by Jeremy Z …
MySQL Optimization by Jeremy Zawodny at Yahoo. He was instrumental- InnoDB: Starting shutdown…
Calling all MySQL admins! When you're working with InnoDB tables- Simple MySQL Backup
If, like me, you have a database full of small
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 and funny videos to explore too!
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)