Getting rid of duplicate records in a SQL Table


By David Fekke
January 17th, 2011

Here is a simple way of getting rid of duplicate records in a SQL Server table. This comes up frequently when a customer gives you bad data. You can clean this up in temp tables before adding it to the actual database.

DELETE temp_Table

WHERE SOMEUNIQUECODE IN (SELECT SOMEUNIQUECODE

FROM temp_Table

GROUP BY SOMEUNIQUECODE

HAVING COUNT(SOMEUNIQUECODE) > 1)

AND tempPrimaryKeyID NOT IN (SELECT MIN(tempPrimaryKeyID)

FROM temp_Table

GROUP BY SOMEUNIQUECODE

HAVING COUNT(SOMEUNIQUECODE) > 1);

GO

So by using the HAVING clause, you can check for a count greater than one on the column that has been duplicated using the COUNT() aggregate function. You can then filter out the records you want to keep by using the MIN() aggregate function on the column that is truly unique.
← Previous Page  Next Page →