Delete-Select-Duplicate-Records-by-CTE-in-SQL-SERVER 2005

Delete Duplicate records from table with CTE. As I explain in my previous article that selecting and deleting duplicate records could be done by several ways so this is another way to do the same.

You can refer that article by following link.
http://riteshshah.wordpress.com/2009/02/28/select-duplicate-records-in-ms-sql-server/
OR
http://ritesh-a-shah.blogspot.com/2009/02/sometime-we-require-finding-duplicate.html

As this is going to be done by CTE, You can get more information about CTE by following link.
http://riteshshah.wordpress.com/2009/02/28/cte-common-table-expression-sql-server-2005/
OR
http://ritesh-a-shah.blogspot.com/2009/02/cte-common-table-expression-sql-server.html

Well, you can use the same table structure and record sets given in my previous article on the nearly same topic.
http://riteshshah.wordpress.com/2009/02/28/select-duplicate-records-in-ms-sql-server/
OR
http://ritesh-a-shah.blogspot.com/2009/02/sometime-we-require-finding-duplicate.html

Once you create table given in above link, you can use the below given query for selecting or deleting records.

WITH DeleteDuplicateRecords AS

(

select Fname,id,Row_number() over (partition by Fname,Lname order by Fname,Lname) as RowNum

from SelectDuplicate

)

Select * from DeleteDuplicateRecords where RowNum>1

Or

WITH DeleteDuplicateRecords AS

(

select Fname,id,Row_number() over (partition by Fname,Lname order by Fname,Lname) as RowNum

from SelectDuplicate

)

DELETE from DeleteDuplicateRecords where RowNum>1

Reference: Ritesh Shah