How to delete duplicate rows using CTE in SQL Server?

In this post, I will explain How to delete duplicate rows using CTE in SQL Server.

A CTE stands for Common Table Expression. A CTE is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. A CTE always returns a result set.

 In below example, I will explain the use of CTE to delete duplicate rows from a table. In an example, I have table tbl_Student with two columns Name & BirthDate. In the table, tbl_Student have some duplicate rows. To achieve this I used CTE with SQL function ROW_NUBER() over partition by Name & BirthDate.

Table tbl_Student SQL Script

CREATE TABLE tbl_Student(

            Name varchar(100) NULL,

            BirthDate date NULL

)

  

INSERT dbo.tbl_Student (Name, BirthDate) VALUES (N'Rakesh', CAST(N'1990-03-12' AS Date))

INSERT dbo.tbl_Student (Name, BirthDate) VALUES (N'Nilesh', CAST(N'1978-03-20' AS Date))

INSERT dbo.tbl_Student (Name, BirthDate) VALUES (N'Sachin', CAST(N'1996-07-12' AS Date))

INSERT dbo.tbl_Student (Name, BirthDate) VALUES (N'Harshal', CAST(N'1989-05-12' AS Date))

INSERT dbo.tbl_Student (Name, BirthDate) VALUES (N'Rajesh', CAST(N'1985-03-05' AS Date))

INSERT dbo.tbl_Student (Name, BirthDate) VALUES (N'Rakesh', CAST(N'1996-03-08' AS Date))

INSERT dbo.tbl_Student (Name, BirthDate) VALUES (N'Rakesh', CAST(N'1990-03-12' AS Date))

INSERT dbo.tbl_Student (Name, BirthDate) VALUES (N'Nilesh', CAST(N'1978-03-20' AS Date))

INSERT dbo.tbl_Student (Name, BirthDate) VALUES (N'Sachin', CAST(N'1996-07-12' AS Date))

INSERT dbo.tbl_Student (Name, BirthDate) VALUES (N'Harshal', CAST(N'1989-05-12' AS Date))

INSERT dbo.tbl_Student (Name, BirthDate) VALUES (N'Rajesh', CAST(N'1985-03-05' AS Date))

INSERT dbo.tbl_Student (Name, BirthDate) VALUES (N'Sachin', CAST(N'1996-07-12' AS Date))

INSERT dbo.tbl_Student (Name, BirthDate) VALUES (N'Sachin', CAST(N'1996-07-12' AS Date))

Image 1 shows a table with data.

With CTE AS  

( 

SELECT          Name, BirthDate, ROW_NUMBER() OVER (PARTITION BY Name, BirthDate ORDER BY Name, BirthDate) AS SeqNo

FROM tbl_Student

) 

DELETE FROM CTE WHERE SeqNo > 1 ;

Image 2 shows a result set CTE.

 Image 3 shows the table after deleting duplicate rows.