Delete duplicate records from a sql table using cte

Here is a simle query to delete duplicate records from sql table using CTE(Compatible for  sql 2005 and 2008)

 

;WITH emptable (FirstName,LastName, dpcount)

AS

(

SELECT FirstName,LastName,

ROW_NUMBER()OVER(PARTITIONBY FirstName, LastName ORDERBY FirstName) AS dpcount

FROM emptable(table name)

)

DELETE

FROM emptable

WHERE dpcount> 1

 

 

 

Enjoy coding..