Tuesday, March 15, 2011

Delete Duplicate record but keep the original record

Straight forward situation when you have the duplicates in a table, but you want to keep just one instance of a row, and delete others

lets say the base table is like this

id name sex
1 mac M
1 mac F
2 manu F
2 manu F
2 manu F
3 chester M
3 chester M

Lets call it SOURCE table, lets put a relative instance count by using row count

;with cte as
 Select id, row_number() over(partition by id order by id) As Rownumber
 ,Row_number() over (order by id) As R2
 ,(select max(id) from SOURCE) AS MaxId
 from SOURCE

--now delete the records for which the instance count is greater than 1

Delete cte
where rownumber > 1

that's it.....

Several variances of same may help you, provide new id's to duplicates, and do a lot of other kind of stuff with duplicate data, do as you will................

Courtesy - Ahmad osama @ http:\\sqlcookbook.wordpress.com

No comments: