CODEWITHSUNDEEP
sqlsql-server
Neso Tomic
Neso Tomic

Reputation: 11

Deleting Duplicate Records In SQL Server

I am using the following code to delete records that have duplicate personnel_IDs. I might just be overlooking something.

DELETE p1 
FROM AR_Personnel p1
INNER JOIN AR_Personnel p2 
WHERE p1.personnel_ID = p2.personnel_ID;

Upvotes: 0

Views: 48

Answers (2)

GuidoG
GuidoG

Reputation: 12014

Another option is using group by to collect all dups

select *
-- delete -- ALWAYS test first !!
from   AR_Personnel
where AR_Personnel.PrimaryKeyField not in 
  ( select min(p.PrimaryKeyField)
    from   AR_Personnel p
    group by p.personnel_ID
  )

But using a CTE is probably faster

Upvotes: 0

benjamin moskovits
benjamin moskovits

Reputation: 5458

You may try something like this - its very fast (assuming column i1 is the dups):

 ; with f1 as (select i1 , row_number() over (partition by i1 order by i1 )  rn from t21)
    delete from f1 where rn > 1

This uses a CTE and is probably the most popular current pattern for deleting dups.

Upvotes: 1

Related Questions