• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 351
  • Last Modified:

Deleting "negating rows" in a table

I have data of accounts and adjustments.  Frequently, the adjustments simply cancel out other adjustments of equal opposite value.  In such cases, those data rows can be deleted.  For example,

select account_number, adj_amt from dbo.DATA_TABLE
where account_number = 'AQL74826698'
order by account_number, abs(adj_amt)

GIVES

account_number                                     adj_amt
-------------------------------------------------- ----------------------
AQL74826698                                       -4
AQL74826698                                       4
AQL74826698                                       -8
AQL74826698                                       8
AQL74826698                                       80
AQL74826698                                       -80
AQL74826698                                       -487
AQL74826698                                       1344.6
AQL74826698                                       -1344.6
AQL74826698                                       1497.6
AQL74826698                                       -1497.6

The only row that did not get canceled out was


AQL74826698                                       -487

So all other rows of data can be deleted from the table except it.  Is there some simple but probably clever query that will do such a deletion?

Thanks,

Cipriano
0
cipriano555
Asked:
cipriano555
1 Solution
 
TempDBACommented:
Delete dt
from dbo.DATA_TABLE dt1
inner join dbo.DATA_TABLE dt2
on dt1.account_number = dt2.account_number
and dt1.adj_amt = (-1 * dt2.adj_amt)
--where account_number = 'AQL74826698'
0
 
tomcahillCommented:
delete from dbo.DATA_TABLE where account_number = 'AQL74826698' and adj_amt != '-487'
0
 
cipriano555Author Commented:
Brilliant!
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now