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.
You could prevent future duplicates and wipe out duplicates in one swoop with something like this as well:
ALTER IGNORE TABLE myTable ADD UNIQUE INDEX(id);
Matt, wouldn’t Aaron’s way be easier? Is there any performance advantage to the way you wrote v. his?
its working really………thanks foe sharing ….thanks a lot.
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)