Deleting Duplicate Records

I was helping someone out today on Experts-Exchange who had a quite a few duplicate records in a table that he wanted to get rid of.  Here’s an example of the data.

SELECT * FROM People
GO

LastName FirstName
-------- ---------
Smith    John
Smith    John
Smith    John
Jackson  Tom
Jones    Sarah
Jones    Sarah
Williams Jessica

As you can see, there is nothing in this table that makes each row unique, and therefore you cannot delete one of the records for John Smith without deleting both records.

The easiest way to delete these duplicates is to add a new IDENTITY column to the table.

ALTER TABLE People ADD ID INT IDENTITY(1,1)
GO

SELECT * FROM People
GO

ID          LastName FirstName
----------- -------- ---------
1           Smith    John
2           Smith    John
3           Smith    John
4           Jackson  Tom
5           Jones    Sarah
6           Jones    Sarah
7           Williams Jessica

This will provide us with a unique value across all records, which we can use to find and delete the duplicate rows.

DELETE FROM People WHERE ID NOT IN
  (
  --Return only the ID for records we want to keep
  SELECT ID
    FROM
         (
         --Return one ID for each person
         SELECT MIN(ID) AS ID, LastName, FirstName
           FROM People
           GROUP BY LastName, FirstName
         ) a
  )
GO

SELECT * FROM People
GO

ID          LastName FirstName
----------- -------- ---------
1           Smith    John
4           Jackson  Tom
5           Jones    Sarah
7           Williams Jessica

We are now left with only one record for each person.  You can now drop the ID column from the table.

ALTER TABLE People DROP COLUMN ID
GO

-A

Advertisement