Deleting duplicate rows in SQL Server

by Abe Miessler 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 
FROM
MyTable
LEFT OUTER JOIN
(
   SELECT MIN
(id) as id, Col1, Col2, Col3
   FROM
MyTable
   GROUP BY
Col1, Col2, Col3
) as KeepRows ON
   
MyTable.id= KeepRows.id
WHERE
   
KeepRows.id IS NULL

Hope this helps someone!

Tags:

SQL | StackOverflow.com

Powered by BlogEngine.NET 1.6.0.0