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
- create table Employees
- (
- ID int,
- Name nvarchar(50),
- Gender char(10),
- Salary int
- )
Step 2
- 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
- WITH EmployeesCTE as
- (
- SELECT*, ROW_NUMBER() over (PARTITION BY ID ORDER BY ID) as RowNumber
- FROM Employees
- )
- SELECT * FROM EmployeesCTE
- 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