MySQL Remove Duplicates

If we have a table like that and want to remove rows with duplicate name fields.

+----+--------+
| id | name   |
+----+--------+
| 1  | google |
| 2  | yahoo  |
| 3  | msn    |
| 4  | google |
| 5  | google |
| 6  | yahoo  |
+----+--------+

1) If you want to keep the row with the lowest id value:

DELETE n1 FROM names n1, names n2 
WHERE n1.id > n2.id AND n1.name = n2.name

2) If you want to keep the row with the highest id value:

DELETE n1 FROM names n1, names n2 
WHERE n1.id < n2.id AND n1.name = n2.name

ref: http://stackoverflow.com/questions/4685173/delete-all-duplicate-rows-except-for-one-in-mysql

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s