CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
yashpal
yashpal

Reputation: 336

How to delete duplicate record where PK is uniqueidentifier field

I want to know the way we can remove duplicate records where PK is uniqueidentifier. I have to delete records on the basis of duplicate values in a set of fields.we can use option to get temptable using Row_Number() and except row number one we can delete rest or the records. But I wanted to build one liner query. Any suggestion?

Upvotes: 0

Views: 1758

Answers (2)

Hunter
Hunter

Reputation: 890

delete from table t using table ta where ta.dup_field=t.dup_field and t.pk >ta.pk
;

Upvotes: 0

Taryn
Taryn

Reputation: 247820

You can use CTE to do this, without seeing your table structure here is the basic SQL

;with cte as
(
    select *, row_number() over(partition by yourfields order by yourfields) rn
    from yourTable
)
delete
from cte
where rn > 1

Upvotes: 6

Related Questions