How do I delete the first matching record in Table B for each record in Table A?

Table A contains multiple records that should be deleted from Table B.  However, there can be multiple records in Table B that match a single record in Table A.  I only want to delete the first matching record in Table B for each record in Table A.  If there are 50 records in Table A then a maximum of 50 records should be deleted from Table B.  I'm using the SQL statement below which is deleting more records from Table B than are listed in Table A due to multiple matches.  I can not further restrict the matching criteria in my statement due to limitations in the data.

DELETE FROM [#DraftInvoiceRecords]
FROM [#DraftInvoiceRecords]
    INNER JOIN [#ReversedRecords]
    ON [#DraftInvoiceRecords].employee = [#ReversedRecords].employee
      and [#DraftInvoiceRecords].amount = [#ReversedRecords].amount
      and [#DraftInvoiceRecords].units = [#ReversedRecords].units
ProUAdminAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jimmym715Commented:
"I only want to delete the first matching record in Table B for each record in Table A."

How are you distinguishing the first matching record from subsequent matching records?  Timestamp?  Integer AutoID?  Or just by whichever record is listed first in the result set?
0
the_billCommented:
If you dont have an ID column on #DraftInvoiceRecords, You'll need to add one, thats the only way you can really identify which row to delete. As its a temp table, you should be able to create one on the fly.
that way, you can use

DELETE [#DraftInvoiceRecords]
where DraftInvoiceRecords_ID = (
select top 1 DraftInvoiceRecords_ID
FROM [#DraftInvoiceRecords]
    INNER JOIN [#ReversedRecords]
    ON [#DraftInvoiceRecords].employee = [#ReversedRecords].employee
      and [#DraftInvoiceRecords].amount = [#ReversedRecords].amount
      and [#DraftInvoiceRecords].units = [#ReversedRecords].units)
0
jimmym715Commented:
Actually, I believe the SQL statement provided above would only delete a single record.

If the data has a sequential primary key, rather than a GUID for example, then the delete should be able to be performed using something similar to the following:

DELETE
FROM [#DraftInvoiceRecords]
WHERE id IN (SELECT deleteID
                        FROM (SELECT ir.employee, ir.amount, ir.units, MIN(ir.id) AS deleteID
                                    FROM [#DraftInvoiceRecords] ir
                                    INNER JOIN [#DraftReverseRecords] rr ON ir.employee = rr.employee
                                          AND ir.amount = rr.amount
                                          AND ir.units = rr.units
                                    GROUP BY ir.employee, ir.amount, ir.units
                               ) derivedTable
                   )

The derived table delivers the minimum ID for each set of matches, and the deletion is performed based on that result set.
 
However, if the primary key is a GUID or some other non-sequential primary key, but the data is timestamped, then a query similar to this one should work:
 
DELETE
FROM [#DraftInvoiceRecords]
WHERE id IN (SELECT ir2.id
                        FROM (SELECT ir.employee, ir.amount, ir.units, MIN(ir.dateTime) as minDateTime
                                    FROM [#DraftInvoiceRecords]  ir
                                    INNER JOIN [#DraftReverseRecords] rr ON ir.employee = a.employee
                                          AND ir.amount = a.amount
                                          AND ir.units = a.units
                                    GROUP BY ir.employee, ir.amount, ir.units) derivedTable
                        INNER JOIN [#DraftInvoiceRecords] ir2 ON derivedTable.employee = ir2.employee
                        AND derivedTable.amount = ir2.amount
                        AND derivedTable.units = ir2.units
                        AND derivedTable.minDateTime = ir2.dateTime

With this one, the derived table delivers the minimum date/time stamp for each set of matches.

That derived table is then joined to the table in which the deletion needs to be performed, and the join includes a match on that minimum date/time stamp (yes, there is a tiny risk of deleting more than one record for each set if there are identical records, but that risk is extremely tiny).

This join then allows one to extract the GUIDs or non-sequential primary keys so the delete can be performed based on that result set.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ProUAdminAuthor Commented:
My data has a sequential primary key so the first example provided by jimmym715 worked perfectly.  Thank you very much  jimmym715.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.