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;
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