Blog post

Common Table Expression(CTE) In SQL SERVER For Deleting Duplicate Rows

Posted on Jul 20, 2014
by Harish Kashyap
in Technology
with 4 comments

Common table expression store data in a temporary table whose identity exists only during the query execution .here we store data in it with the fields we required by giving the names and perform the action we want to perform.

Consider  a table tbldata with the following fields

Id

Name

Age

1

Harish

24

5

Yogesh

45

12

Joker

45

25

Joker

45

26

Joker

45

27

Harish

28

 

 

 

 

And we want to delete duplicate rows using CTE .

with T (col1,col2,repeatcount)as

(select name,age,row_number() over (partition by name,age order by name) as temptable from tbldata )

select * from T

Col1

Col2

Repeatcount

Harish

24

1

Harish

28

1

Joker

45

1

Joker

45

2

Joker

45

3

Yogesh

45

1

 

 

here T is the name of the table col1 and col2 are the name of the columns for name and age we can use name of columns as  name and age simply if we want.

Now there is one extra column which is repeatcount it shows how many times the value for name and age repeat in the table.

 

Now to delete it the query is of delete instead of select.

with T (col1,col2,repeatcount)as

(select name,age,row_number() over (partition by name,age order by name) as temptable from tbldata )

delete from T where repeatcount>1

go

select * from tbldata

 

Id

Name

Age

1

Harish

24

5

Yogesh

45

12

Joker

45

27

Harish

28

 

 

 

 

 

Here the value of same name and age deleted .if u want  to delete the repeated name only  then the  query  is

with T (col1,col2,repeatcount)as

(select name,age,row_number() over (partition by name order by name) as temptable from tbldata )

delete from T where repeatcount>1

go

select * from tbldata

same goes for age only.


4

Leave your comment

    Badan Singh
    on Aug 24, 2014
  1. Its very helpful and good post.....

    Badan Singh Pundeer

     

  2. Harish Kashyap
    on Dec 19, 2014
  3. thnks all

  4. Harish Kashyap
    on Dec 19, 2014
  5. thnks all

  6. Pradip Shukla
    on Jul 20, 2014
  7. Its really good post for all who are fighting with duplicate records in SQL Server.This is the simplest way to remove the duplicate data from the database(SQL Server).

Leave your comment