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.
https://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.
https://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.
https://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
November 9, 2012 at 9:37 pm
[…] The second script is and adaptation of Ritesh’s Post on duplicates. […]