CODEWITHSUNDEEP
oracle-database
Racle
Racle

Reputation: 11

Deleting opposite duplicates in Oracle SQL

I have some issues that I hope you can lend a helping hand towards. So I have some data with opposite value, for example:

Amount  Type    ID  
 10000  10      25  
-10000  10      25  
 20000  11      30  
 30000  12      49  
-30000  12      49

Sorry for the ugly table.

But how can I delete the lines where the amount cancelled out? I would like the ones with 10000 and -10000 to be deleted. But I wont know the specific type and id number. (30000 and -30000 is the same issue).

Any ideas? I've been searching forever, but can only find how to remove a duplicate row, and not both rows.

Hope it makes sense :)

Update. Thanks for the solutions so far! :) There can be more than 1:1 in the amount column, but those wouldn't identical Type and ID. For exampel could a 6th entry look like this: 

Amount  Type    ID  
 10000  10      25  
-10000  10      25  
 20000  11      30  
 30000  12      49  
-30000  12      49
 10000  31      42

And the last one should not be deleted :) Hope it makes sense now.

Upvotes: 1

Views: 81

Answers (1)

Strawberry
Strawberry

Reputation: 33935

On the basis only of the limited information provided...

DELETE x 
  FROM my_table x 
  JOIN my_table y 
    ON y.id = x.id 
   AND y.type = x.type 
   AND y.amount = x.amount * -1;

Upvotes: 1

Related Questions