27. July 2010 18:03
I came across this very slick method for deleting duplicate records while poking around on StackOverflow today. I've used a variety of methods in the past for deleting dupes, but nothing quite as clean as this.
The SQL below would work for a table in the following format, you'll need to adjust as necessary. Please note that this will only work if id is a Primary key.
MyTable: id, Col1, Col2, Col3
And the SQL:
DELETE MyTable Hope this helps someone!
LEFT OUTER JOIN (
SELECT MIN(id) as id, Col1, Col2, Col3
GROUP BY Col1, Col2, Col3
) as KeepRows ON
KeepRows.id IS NULL