Wednesday, July 28, 2004

How to Delete Duplicate Record in SQL Server

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


Ritesh Kesharwani
SQL Star Intlernational Ltd.
Riteshk@sqlstarintl.com
Ritesh_kk2000@yahoo.com
www.Riteshk.blogspot.com
Cell - 9849976150

No comments: