Deleting Duplicate rows using CTE

Interview Questions SQL Advanced SQL CTE SQL Server

Deleting Duplicate rows using CTE

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

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.

26 thoughts on “Deleting Duplicate rows using CTE

  1. The “where” clause in the CTE delete is wrong, the field is called “rank” inside the cte but in the where is used “rownumber”

  2. Pingback: ww88 casino
  3. Pingback: ROCHESTER MN homes
  4. Pingback: Empire Market
  5. Pingback: Darknet Drogen
  6. Pingback: cash fish game
  7. Pingback: BQEssentials
  8. Pingback: hondaqq1
  9. Pingback: แทงบอล
  10. Pingback: i99casino
  11. Pingback: My blog
  12. Pingback: raja qq
  13. Pingback: rajacapsa
  14. Pingback: datasgp
  15. Pingback: klikdokter

Leave a Reply