CODEWITHSUNDEEP
sqlsql-server
user13636604
user13636604

Reputation: 19

SQL delete duplicate query

I try to delete duplicate (service column) but not working

DELETE FROM contactactionnodup
WHERE service IN (SELECT service, COUNT(*), contactid
                  FROM ContactActionNoDup 
                  GROUP BY service, contactid
                  HAVING COUNT(*) > 1)

Need to correct this query. Thank you

Upvotes: 0

Views: 44

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

If you want to keep one of the rows for each service/contactid pair, then use an updatable CTE:

with todelete as (
      select ca.*, row_number() over (partition by service, contactid order by service) as seqnum
      from ContactActionNoDup as ca
     )
delete from todelete
    where seqnum > 1;

Upvotes: 2

Related Questions