Delete Duplicate Record From SQL Database Using CTE

Loading

Delete Duplicate Record From SQL Database Using CTE

Introduction

How to delete duplicate records from a SQL data table using CTE (common table expression). Specifies a temporary named result set, known as a common table expression (CTE).

Syntax

WITH table_nameCTE AS
(
SELECT*, ROW_NUMBER() over (PARTITION BY ID ORDER BY ID) as < alias_name >
FROM table_name
)
DELETE FROM table_nameCTE WHERE alias_name >1

Step 1

Open SQL Server 2014 or your choice. Create table with name Employees.

create table Employees
(
ID int,
Name nvarchar(50),
Gender char(10),
Salary int
)

Step 2

Insert some duplicate record. Below is sample record.

insert into Employees values(1,'Farhan Ahmed','Male',60000)
insert into Employees values(5,'Monika','Female',25000)
insert into Employees values(2,'Abdul Raheem','Male',30000)
insert into Employees values(4,'Rahul Sharma','Male',60000)
insert into Employees values(1,'Farhan Ahmed','Male',60000)
insert into Employees values(2,'Abdul Raheem','Male',30000)
insert into Employees values(5,'Monika','Female',25000)
insert into Employees values(4,'Rahul Sharma','Male',60000)
insert into Employees values(1,'Farhan Ahmed','Male',60000)
insert into Employees values(3,'Priya','Female',20000)
insert into Employees values(5,'Monika','Female',25000)
insert into Employees values(4,'Rahul Sharma','Male',60000)
insert into Employees values(5,'Monika','Female',25000)
insert into Employees values(2,'Abdul Raheem','Male',30000)
insert into Employees values(1,'Farhan Ahmed','Male',60000)
insert into Employees values(4,'Rahul Sharma','Male',60000)

Step 3

Write CTE (Common table expression) and partition records.

WITH EmployeesCTE as
(
SELECT*, ROW_NUMBER() over (PARTITION BY ID ORDER BY ID) as RowNumber
FROM Employees
)
SELECT * FROM EmployeesCTE

Delete Duplicate Record From SQL Database Using CTE

Step 4

Write query for delete duplicate record with CTE (common table expression).

WITH EmployeesCTE as
(
SELECT*, ROW_NUMBER() over (PARTITION BY ID ORDER BY ID) as RowNumber
FROM Employees
)
DELETE FROM EmployeesCTE WHERE RowNumber>1
SELECT * FROM Employees

Delete Duplicate Record From SQL Database Using CTE