Most articles on removing duplicate rows from a MySQL table involve 3 steps, but the following query is what I use for purging dupe records in one simple query.
DELETE FROM `myTable` WHERE id NOT IN (SELECT t1.id FROM (SELECT id, groupByColumn FROM `myTable` ORDER BY id DESC) as t1 GROUP BY t1.groupByColumn)
– “myTable” is the name of the table with duplicate rows
– “id” is the name of the primary key identifier in “myTable”
– “groupByColumn” is the name of the column used to differentiate records as duplicates
Example: Table of Videos with the duplicate match being made on the “title” field.
DELETE FROM `videos` WHERE id NOT IN (SELECT t1.id FROM (SELECT id, title FROM `videos` ORDER BY id DESC) as t1 GROUP BY t1.title)
It’s a good SQL query to save or bookmark for those times when you need do some maintenance or cleanup during development.