1

How can I remove all the records with duplicates in db2. I have looked at various answers but they only remove the duplicates leaving one record from that set in the table. This is what I found already.

DELETE FROM (SELECT ROWNUMBER() OVER (PARTITION BY ONE, TWO, THREE) AS RN FROM SESSION.TEST) AS A WHERE RN > 1;

But, I need a query that will remove all the records that contain duplicates not leaving behind one of them in the table.

A A 1 <-- delete this
A A 2 <-- delete this too
B B 3
C C 4

P.S: Using RN >= 1 does not work as it will make the table empty by deleting all records.

2

Assuming your desired uniqueness constraint is (one,two), here is one way to do it:

DELETE FROM session.test WHERE (one, two) IN ( SELECT one, two FROM session.test GROUP BY one, two HAVING COUNT(*) > 1 )

  • 1
    Nice! I was stumped myself on trying to figure this one out. Having tested it, I can say it works. – Chris Aldrich Jan 23 '15 at 14:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.