Delete all duplicate rows except for one in MySQL

Delete all duplicate rows except for one in MySql having no primary key.

Someone asked me to delete all rows from a table except one entry with a single query.
This is not so easy for me :), Anyway after work and research I have found the solution, I am sharing with you How you can do the same.

Let’s take an example of table tbl_employee have duplicate names data as follows

 

SELECT * FROM tbl_employee;
——————–
id    name
——————–
1     mahesh
2     suresh
3     john
4     john
5     john
6     mahesh
——————–

 

If you want to keep the row with the lowest id value then

DELETE e1 FROM tbl_employee e1, tbl_employee e2 WHERE e1.id > e2.id AND e1.name = e2.name

Or with highest id value

DELETE e1 FROM tbl_employee e1, tbl_employee e2 WHERE e1.id < e2.id AND e1.name = e2.name

2 thoughts on “Delete all duplicate rows except for one in MySQL”

  1. Thanks scriptarticle for sharing such a great article.

    Same question have asked me many times by interviewer in interviews. I’ll give right answer from today 🙂

    Please share more article.. I have enjoyed other articles also.

Leave a Reply

Your email address will not be published. Required fields are marked *