Deleting Duplicates

[Tuesday, October 26, 2010]

Of course we ought to prevent duplicates from happening in the first place but if requirements change or things go wrong, here's an example of how you can easily find or delete duplicates:

DELETE FROM dupes
FROM( SELECT empID, fname, lname,
        ROW_NUMBER() OVER( PARTITION BY fname, lname ORDER BY empID ASC ) AS nmbr 
      FROM tblEmployees ) AS dupes
WHERE dupes.nmbr > 1

I wrote this example for MS SQL 2005. it should also work in 2008. I never tried in 2000.

Posted by: Benjamin Felt