Remove duplicate entries. Assume the following table and data. 
CREATE TABLE IF NOT EXISTS dupTest ( 
pkey int(11) NOT NULL auto_increment, 
a int, 
b int, 
c int, 
timeEnter timestamp(14), 
PRIMARY KEY (pkey) 
 
); 
insert into dupTest (a,b,c) values (1,2,3),(1,2,3), 
(1,5,4),(1,6,4); 
mysql> select * from dupTest; 
select * from dupTest; 
+------+------+------+------+---------------------+ 
 | 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 TABLE dupTest ADD UNIQUE INDEX(a,b); 
mysql> select * from dupTest; 
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) 
 
Sunday, April 27, 2008
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment