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.
