Wednesday, March 18, 2009

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: