Post
Ask Question

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

  1. WITH table_nameCTE AS  
  2. (  
  3.    SELECT*, ROW_NUMBER() over (PARTITION BY ID ORDER BY ID) as < alias_name >  
  4.    FROM table_name  
  5. )  
  6. DELETE FROM table_nameCTE WHERE alias_name >1  

Step 1

Open SQL Server 2014 or your choice. Create table with name Employees.
  1. create table Employees  
  2. (  
  3.    ID int,  
  4.    Name nvarchar(50),  
  5.    Gender char(10),  
  6.    Salary int  
  7. )  

Step 2

Insert some duplicate record. Below is sample record.
  1. insert into Employees values(1,'Farhan Ahmed','Male',60000)  
  2. insert into Employees values(5,'Monika','Female',25000)  
  3. insert into Employees values(2,'Abdul Raheem','Male',30000)  
  4. insert into Employees values(4,'Rahul Sharma','Male',60000)  
  5. insert into Employees values(1,'Farhan Ahmed','Male',60000)  
  6. insert into Employees values(2,'Abdul Raheem','Male',30000)  
  7. insert into Employees values(5,'Monika','Female',25000)  
  8. insert into Employees values(4,'Rahul Sharma','Male',60000)  
  9. insert into Employees values(1,'Farhan Ahmed','Male',60000)  
  10. insert into Employees values(3,'Priya','Female',20000)  
  11. insert into Employees values(5,'Monika','Female',25000)  
  12. insert into Employees values(4,'Rahul Sharma','Male',60000)  
  13. insert into Employees values(5,'Monika','Female',25000)  
  14. insert into Employees values(2,'Abdul Raheem','Male',30000)  
  15. insert into Employees values(1,'Farhan Ahmed','Male',60000)  
  16. insert into Employees values(4,'Rahul Sharma','Male',60000)  

Step 3

Write CTE (Common table expression) and partition records.
  1. WITH EmployeesCTE as  
  2. (  
  3.    SELECT*, ROW_NUMBER() over (PARTITION BY ID ORDER BY ID) as RowNumber  
  4.    FROM Employees  
  5. )  
  6. SELECT * FROM EmployeesCTE  
Delete Duplicate Record From SQL Database Using CTE
 
Step 4
 
Write query for delete duplicate record with CTE (common table expression).
  1. WITH EmployeesCTE as  
  2. (  
  3.    SELECT*, ROW_NUMBER() over (PARTITION BY ID ORDER BY ID) as RowNumber  
  4.    FROM Employees  
  5. )  
  6. DELETE FROM EmployeesCTE WHERE RowNumber>1  
  7.   
  8. SELECT * FROM Employees  
Delete Duplicate Record From SQL Database Using CTE
Next Recommended Reading Find Duplicate records from a Database Table and Delete them.