TSQL – Delete Duplicate Rows

SOURCE: TSQL – How To Delete All Duplicate Records From a SQL Server Table

Here are couple of ways to delete duplicate records from a SQL Server table.

–Prepare Sample Data
USE TestDB

CREATE TABLE dbo.Customer(
ID INT,
FirstName VARCHAR(100),
LastName VARCHAR(100),
Age INT)

GO

Insert dummy data

INSERT INTO dbo.Customer
VALUES(1,'Aamir','Shahzad',34)
,(1,'Aamir','Shahzad',34)
,(2,'Raza','M',32)
,(3,'Sukhjeet','Singh',27)
,(4,'Sukhjeet','Singh',28)

Delete all duplicate records by using Common Table Expressions(CTE):

;WITH CTE        
AS (     SELECT FirstName,LastName,Row_number()
OVER (
PARTITION BY FirstName, LastName    
ORDER BY (SELECT 1) ) AS Rn
FROM   dbo.Customer)       

DELETE FROM       CTE
WHERE EXISTS (
SELECT 1 FROM cte t
WHERE t.FirstName=cte.FirstName
AND t.LastName=cte.LastName AND rn>1
)

 

Delete all duplicate records by using Group By and Left Join:

DELETE FROM C   
FROM dbo.Customer C         
LEFT JOIN     (   
SELECT FirstName,LastName,COUNT(*) AS RecordCnt    
FROM   dbo.Customer    
GROUP  BY FirstName,LastName    
HAVING COUNT(*) > 1
) DR    ON DR.FirstName=C.FirstName    AND DR.LastName=C.LastName   
WHERE DR.FirstName IS NOT NULL   
AND DR.LastName IS NOT NULL