Deleting duplicate rows


There are times when we have some how duplicate data in a table where each row is same and you want to delete duplicate rows form the table. This article outlines one way in which you can accomplish this task using ROW_NUMBER().

There are times when we have some how duplicate data in a table where each row is same and you want to delete duplicate rows form the table. This article outlines one way in which you can accomplish this task using ROW_NUMBER().

For example we have a following table:


Create Table T1
(
col1 varchar(20),
col2 varchar(20)
)


Now lets assume that some how you inserted duplicate data into this table like below:

col1 col2
aa bb
aa bb
aa bb
dd cc
dd cc

Using ROW_NUMBER() we can differentiate duplicate rows as shown in the following query:

Select ROW_NUMBER() over (PARTITION BY col1,col2 ORDER BY col1) R,
col1,
col2
From T1


Above query will return the following result:

R col1 col2
1 aa bb
2 aa bb
3 aa bb
1 dd cc
2 dd cc

Now if you want to delete the duplicate rows from this table then you can do this using ROW_NUMBER as shown in below
query:


Delete t
From (
Select ROW_NUMBER() over (PARTITION BY col1,col2 ORDER BY col1) R,
col1,
col2
From T1
) t
Where R>1


Comments

Author: sridhar thota02 May 2015 Member Level: Gold   Points : 3

1.If we have a original table let us say "emp_
details" with duplicate records.

2.We will copy unique records in to a temporary
table let us say "newEmp_details".


select * into newEmp_details
from(select distinct * from emp_details)a
Delete emp_details


3.Now we will copy unique records back to original table.


select * into emp_details
from newEmp_details


Regards

Sridhar.
DNS Member.
"Hope for the best.. Prepare for the worst.."



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: