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”
Pingback: tructiepbongda tructuyen
Pingback: Car Crashes Compilation Russia/USA/Europe 2019 HD
Pingback: ww88 casino
Pingback: บริษัท ออล ครีเอตี้ แลนด์ จำกัด
Pingback: https://surfwearhawaii.com
Pingback: ROCHESTER MN homes
Pingback: след лазерна епилация
Pingback: p243#https://strefarelaksacyjna.pl/forum/wystroj-wnetrz-f6/pulapki-na-karaluchy-i-rybiki-t160.html#p243
Pingback: Empire Market
Pingback: Crystal Meth kaufen
Pingback: Darknet Drogen
Pingback: Dream Market Deutsch
Pingback: Samsara Market Exit Scam
Pingback: The Majestic Garden
Pingback: cash fish game
Pingback: BQEssentials
Pingback: hondaqq1
Pingback: แทงบอล
Pingback: i99casino
Pingback: My blog
Pingback: raja qq
Pingback: rajacapsa
Pingback: đánh bài mậu binh online
Pingback: datasgp
Pingback: klikdokter