Delete or remove duplicate rows in MySQL

You can easily remove duplicate rows in MySQL and keep just one record for each. I have provided two solutions here, one is simple query and second is using subquery to attain the same.

1. Using Query

DELETE t1 FROM yourtable, yourtable t2 
WHERE  t1.column_name = t2.column_name AND t1.id > t2.id

1 2	DELETE t1 FROM yourtable, yourtable t2 WHERE t1.column_name = t2.column_name AND t1.id > t2.id

This will remove all the duplicate entries in your MySQL database while keeping row with the lowest id value.

If you wish to delete duplicate rows except row with highest id value, you can alter the query in following manner:

DELETE t1 FROM yourtable, yourtable t2 
WHERE t1.column_name = t2.column_name AND t1.id < t2.id

1 2	DELETE t1 FROM yourtable, yourtable t2 WHERE t1.column_name = t2.column_name AND t1.id < t2.id

2. Using Subquery

Keep the row with the lowest id value and remove duplicate rows:

DELETE FROM yourtable WHERE id NOT IN 
(SELECT * FROM 
(SELECT MIN(t1.id) FROM yourtable t1 GROUP BY t1.column_name) t2)

DELETE FROM yourtable WHERE id NOT IN

(SELECT * FROM

(SELECT MIN(t1.id) FROM yourtable t1 GROUP BY t1.column_name) t2)

Keep the row with the highest id value and delete duplicate rows:

DELETE FROM yourtable WHERE id NOT IN 
(SELECT * FROM 
(SELECT MAX(t1.id) FROM yourtable t1 GROUP BY t1.column_name) t2)

DELETE FROM yourtable WHERE id NOT IN

(SELECT * FROM

(SELECT MAX(t1.id) FROM yourtable t1 GROUP BY t1.column_name) t2)

The subquery in a subquery is necessary for MySQL, or you’ll get a 1093 error.

While performing any of these queries, you should do this first on a test copy of your table.

Leave a Reply Cancel reply