TechRawr

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.