Search

Thursday, September 22, 2011

Delete Duplicate Records Using Common Tabel Expression


There are many ways to delete the duplicates in a table but sometimes the group by doesnt seem to work so you can try out the new feature of Microsoft SQL Server "Common Table Expression"

With Dups as 
(
SELECT  row_number() over (
partition by ColumnName 
order by  ColumnName ) as RowNum
FROM Table
)
DELETE 
FROM Dups 
WHERE rownum > 1

No comments:

Post a Comment