How To Delete All Duplicate Records From a SQL Server Table

How To Delete All Duplicate Records From a SQL Server Table

Multiple ways to delete duplicate records from a SQL Server table.

--Prepare Sample Data

Create Database SampleDB

USE SampleDB

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

GO

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