SELECT Delegate.DelegateID, Delegate.FirstName, Delegate.LastName FROM Delegate INNER JOIN (SELECT FirstName, LastName FROM Delegate GROUP BY FirstName, LastName HAVING Count(DelegateID) > 1 ) AS Duplicates ON Delegate.FirstName = Duplicates.FirstName AND Delegate.LastName = Duplicates.LastName
DELETE FROM Delegate WHERE DelegateID = ANY ( SELECT DupDels.DelegateID FROM DupDels LEFT JOIN (SELECT Min(DelegateID) AS DelegateID, FirstName, LastName FROM DupDels GROUP BY FirstName, LastName) AS FirstDup ON DupDels.DelegateID = FirstDup.DelegateID WHERE FirstDup.DelegateID IS NULL )
This is general discussion. You should translate it to your case by yourself.
CREATE TABLE Dups ( ID INT IDENTITY(1,1) PRIMARY KEY, A VARCHAR(30), B VARCHAR(30), C VARCHAR(30) ) go INSERT INTO Dups(A,B,C) VALUES ('hi','there','all') INSERT INTO Dups(A,B,C) VALUES ('hi','there','all') INSERT INTO Dups(A,B,C) VALUES ('bye','from','me') INSERT INTO Dups(A,B,C) VALUES ('repeated','three','times') INSERT INTO Dups(A,B,C) VALUES ('repeated','three','times') INSERT INTO Dups(A,B,C) VALUES ('repeated','three','times') -- Duplicate rows - one from each group SELECT * FROM Dups T WHERE ID > ( SELECT MAX(ID) FROM Dups I WHERE I.A = T.A AND I.B = T.B AND I.C = T.C AND I.ID <> T.ID ) -- Non-duplicated view - one row from each group SELECT * FROM Dups T WHERE ID >= ALL ( SELECT ID FROM Dups I WHERE I.A = T.A AND I.B = T.B AND I.C = T.C ) -- Rows to be deleted - usefull for COUNT(*) and DELETE SELECT * FROM Dups T WHERE ID < ( SELECT MAX(ID) FROM Dups I WHERE I.A = T.A AND I.B = T.B AND I.C = T.C AND I.ID <> T.ID ) -- Delete the duplicates DELETE Dups WHERE ID < ( SELECT MAX(ID) FROM Dups I WHERE I.A = Dups.A AND I.B = Dups.B AND I.C = Dups.C AND I.ID <> Dups.ID )
file: /Techref/language/sql/tsqldups.htm, 3KB, , updated: 2016/12/9 15:58, local time: 2024/9/9 08:31,
owner: JMN-EFP-786,
18.118.19.136:LOG IN
|
©2024 These pages are served without commercial sponsorship. (No popup ads, etc...).Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE. Questions? <A HREF="http://linistepper.com/techref/language/sql/tsqldups.htm"> Duplicates</A> |
Did you find what you needed? |