SQL delete duplicate query

Question

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

Eric Brandt · Accepted Answer · 2020-06-22 19:20:42Z

2

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;

edited Jun 22, 2020 at 19:20

Eric Brandt

8,1313 gold badges20 silver badges39 bronze badges

answered Jun 22, 2020 at 19:16

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user13636604 Over a year ago

what is ca.* and I don't understand

Collectives™ on Stack Overflow

SQL delete duplicate query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related