Showing posts with label Delete duplicate rows using CTE. Show all posts
Showing posts with label Delete duplicate rows using CTE. Show all posts

Monday 24 July 2017

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.

delete duplicate rows using CTE

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.

CTE

 Image 3 shows the table after deleting duplicate rows.

CTE