Deleting "negating rows" in a table

Posted on 2012-09-18
Last Modified: 2012-09-19
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)


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?


Question by:cipriano555
    LVL 25

    Accepted Solution

    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'
    LVL 9

    Expert Comment

    delete from dbo.DATA_TABLE where account_number = 'AQL74826698' and adj_amt != '-487'

    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now