Deleting Duplicate rows using CTE
Suppose we have a table name “tbl_dublicatedata” whose table creation script is given below:-
Create table tbl_dublicatedata (name nvarchar(100), age int, salary int)
Now we insert duplicate data into this table. Insert script for these rows are given below:-
Insert into tbl_dublicatedata
select ‘Vivek’, 28, 800000
union all
select ‘Avinash’, 29, 600000
union all
select ‘Vivek’, 28, 800000
If we want to see the data into the table , we can run the following query
select * from tbl_dublicatedata
This query will return the following result
With the help of the CTE we can remove these duplicate records.
With cte_duplicate (name, age, salary, rownumber)
as (
select name,age,salary, row_number()over(partition by name, age , salary order by name, age , salary)as rank from tbl_dublicatedata
)
delete from cte_duplicate where rownumber<>1
If we again see the data in the table tbl_dublicatedata using the same query which we used earlier we will get the following result:-
select * from tbl_dublicatedata
From the result we can see that the duplicate rows are deleted and there is no duplicate data in the table.
The “where” clause in the CTE delete is wrong, the field is called “rank” inside the cte but in the where is used “rownumber”