How to Remove duplicate entries?
by Sanju[ Edit ] 2008-10-13 19:34:35
Remove Duplicate Entries
Assume the following table and data:
Create table test (EmpNo int(11) NOT NULL auto_increment, Name varchar(50), Post varchar(50), TimeEnter timestamp(14), PRIMARY KEY (pkey));
insert into test (Name, Post) values (Sanju, Software Engineer),(Raj, CEO),(Sanjesh, SEO Engineer),(Senthil, Manager),(Sanjesh, SEO Engineer);
mysql> select * from test;
select * from test;
+--------+----------+--------------------+---------------------+
| Emp no | Name | Post | timeEnter |
+--------+----------+--------------------+---------------------+
| 1 | Sanju | Software Engineer | 2003-04-16 10:55:35 |
| 2 | Raj | CEO | 2005-04-16 10:55:35 |
| 3 | Sanjesh | SEO Engineer | 2004-04-16 10:55:40 |
| 4 | Senthil | Manager | 2005-04-16 10:55:35 |
| 5 | Sanjesh | SEO Engineer | 2004-04-16 10:55:40 |
+--------+----------+--------------------+---------------------+
4 rows in set (0.00 sec)
Note, the third and fifth rows contains duplicates in columns Name and Post. It contains other duplicates; but, leaves the other duplicates alone.
ALTER IGNORE TABLE test ADD UNIQUE INDEX(Name,Post);
mysql> select * from test;
select * from test;
+--------+----------+--------------------+---------------------+
| Emp no | Name | Post | timeEnter |
+--------+----------+--------------------+---------------------+
| 1 | Sanju | Software Engineer | 2003-04-16 10:55:35 |
| 2 | Raj | CEO | 2005-04-16 10:55:35 |
| 3 | Sanjesh | SEO Engineer | 2004-04-16 10:55:40 |
| 4 | Senthil | Manager | 2005-04-16 10:55:35 |
+--------+----------+--------------------+---------------------+
4 rows in set (0.00 sec)
