Deleting Duplicate rows using CTE

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.

About vivekjohari

Database Consultant with more than 11.5 years of experience in database designing & programming and DBA related activities.  Had good experience on different databases like SQL Server, MySQL & Oracle, Azure SQL &  Big Data.
This entry was posted in Interview Questions, SQL Advanced, SQL CTE, SQL Server and tagged . Bookmark the permalink.

26 Responses to Deleting Duplicate rows using CTE

  1. Anonymous says:

    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: tructiepbongda tructuyen

  3. Pingback: Car Crashes Compilation Russia/USA/Europe 2019 HD

  4. Pingback: ww88 casino

  5. Pingback: บริษัท ออล ครีเอตี้ แลนด์ จำกัด

  6. Pingback:

  7. Pingback: ROCHESTER MN homes

  8. Pingback: след лазерна епилация

  9. Pingback: p243#

  10. Pingback: Empire Market

  11. Pingback: Crystal Meth kaufen

  12. Pingback: Darknet Drogen

  13. Pingback: Dream Market Deutsch

  14. Pingback: Samsara Market Exit Scam

  15. Pingback: The Majestic Garden

  16. Pingback: cash fish game

  17. Pingback: BQEssentials

  18. Pingback: hondaqq1

  19. Pingback: แทงบอล

  20. Pingback: i99casino

  21. Pingback: My blog

  22. Pingback: raja qq

  23. Pingback: rajacapsa

  24. Pingback: đánh bài mậu binh online

  25. Pingback: datasgp

  26. Pingback: klikdokter

Leave a Reply