Many times we caught in a situation where we have a table where there is no primary or unique column defined in it and rows with duplicate data inserted in the table. In this example I will tries to show how to delete the 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
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.
