Find duplicates in a table and delete them using SQL Server 2005

It is a very common requirement to find duplicates in table based on a column or set of columns and then delete them. Let us see how to do that:

Sample Table

DROP TABLE #Customers
GO

CREATE TABLE #Customers (CustID int, CustName varchar(10), PostalCode int)
INSERT #Customers SELECT 1,'A',100
INSERT #Customers SELECT 1,'B',100
INSERT #Customers SELECT 2,'C',100
INSERT #Customers SELECT 3,'D',100
INSERT #Customers SELECT 4,'E',100
INSERT #Customers SELECT 5,'F',100
INSERT #Customers SELECT 5,'G',100
INSERT #Customers SELECT 6,'H',200
INSERT #Customers SELECT 7,'I',300

Find Duplicates in CustID

SELECT CustID
FROM #Customers
GROUP BY CustID HAVING count(*) > 1

returns you the duplicate CustID

Delete Duplicates in the Customer table

SET ROWCOUNT 1
SELECT NULL
WHILE @@rowcount > 0
DELETE cust
FROM #Customers as cust
INNER JOIN
(SELECT CustID
FROM #Customers
GROUP BY CustID HAVING count(*) > 1)
AS c ON c.CustID = cust.CustID
SET ROWCOUNT 0

SELECT * FROM #Customers

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook