How to Remove duplicate entries?

by Sanju 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)


Tagged in:

1580
like
0
dislike
0
mail
flag

You must LOGIN to add comments