Removing entries from a table where it's value

2019-09-04 17:00发布

I'm starting with this example table (#temp2):

| a | b |
|---|---|
| 2 | 4 |
| 2 | 5 | x
| 3 | 1 |
| 6 | 4 | x
| 6 | 5 |
| 7 | 5 | x
| 7 | 4 | x
|---|---|

This is a table of transaction keys that I want to be deleted from another existing table. It represents transactions that negate other transactions, where a negates b or vice-versa. So I cannot have a single a negating multiple b or a single b negating multiple a. I have some logic that I thought would do it but there is a problem. With my existing logic it will look to see if a or b is a duplicate and delete it if it is. The problem is, if I want a row to be deleted I would like it to 'free' up the value that wasn't the reason for deletion. I hope this isn't too confusing. But from my example I put x's next to each row I want deleted. Currently my algorithm is deleting too many rows. The row (6,5) gets deleted because there already exists a '5' in the second row, but that row is getting deleted (since '2' can't negate '4' and '5') so this 'frees up' the '5' to negate entry 6.

This is my current code, but it is deleting too many rows:

delete t
from #temp2 t
    where exists(select * from #temp2 
                 where b = t.b 
                    and a < t.a)
  or exists(select * from #temp2
                 where a = t.a 
                    and b < t.b)

Any help is much appreciated!

0条回答
登录 后发表回答