LISTING 1: Using a Correlated Subquery to Delete Duplicate Records

CREATE TABLE Dups (NameId int, FirstName varchar(10), LastName varchar(10))
GO

INSERT INTO Dups VALUES (106,         'Robert',                                       'Aide')
INSERT INTO Dups VALUES (107,         'David',                                        'Koidl')
INSERT INTO Dups VALUES (108,         'Hongbo',                                       'Li')
INSERT INTO Dups VALUES (123,         'Robert',                                       'Aide')
INSERT INTO Dups VALUES (124,         'David',                                        'Koidl')
INSERT INTO Dups VALUES (125,         'Hongbo',                                       'Li')
INSERT INTO Dups VALUES (138,         'Robert',                                       'Aide')
INSERT INTO Dups VALUES (139,         'David',                                        'Koidl')
INSERT INTO Dups VALUES (140,         'Hongbo',                                       'Li')
INSERT INTO Dups VALUES (153,         'Robert',                                       'Aide')
INSERT INTO Dups VALUES (154,         'David',                                        'Koidl')
INSERT INTO Dups VALUES (155,         'Hongbo',                                       'Li')
 GO

DELETE FROM dups
WHERE
 EXISTS
 (
 SELECT 
  NameId
 FROM
  dups DupsInner
 WHERE
  DupsInner.FirstName  = Dups.FirstName
  AND DupsInner.LastName  = Dups.LastName
  AND DupsInner.NameId < Dups.NameId 
 )
GO