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
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".
3.Now we will copy unique records back to original table.
Regards
Sridhar.
DNS Member.
"Hope for the best.. Prepare for the worst.."