TSQL - How To Keep One Record From Duplicate Records and Remove All Of Others From a SQL Server Table

We have a SQL Server Table from which has duplicate records. We want to keep only one records from duplicate records and delete all others.

We can use Common Table Expressions ( CTE) with Row_Number() function to achieve this


Let's create a SQL Server table with duplicate records

USE TestDB

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',28)
,(4,'Sukhjeet','Singh',28)

Write our CTE to Mark the duplicate records for Delete

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


Delete Duplicate Records and keep only one copy
;WITH CTE
            AS (
    SELECT FirstName,
                LastName,
                Age,
                Row_number()
                OVER (
                    PARTITION BY FirstName, LastName,Age
            ORDER BY (
            SELECT 1) ) AS Rn
        FROM   dbo.Customer)
DELETE FROM CTE WHERE Rn>1
As we can see, all the duplicate copies are deleted and only unique records are left.