ProUAdmin
asked on
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].emp
and [#DraftInvoiceRecords].amo
and [#DraftInvoiceRecords].uni
DELETE FROM [#DraftInvoiceRecords]
FROM [#DraftInvoiceRecords]
INNER JOIN [#ReversedRecords]
ON [#DraftInvoiceRecords].emp
and [#DraftInvoiceRecords].amo
and [#DraftInvoiceRecords].uni
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].emp
and [#DraftInvoiceRecords].amo
and [#DraftInvoiceRecords].uni
that way, you can use
DELETE [#DraftInvoiceRecords]
where DraftInvoiceRecords_ID = (
select top 1 DraftInvoiceRecords_ID
FROM [#DraftInvoiceRecords]
INNER JOIN [#ReversedRecords]
ON [#DraftInvoiceRecords].emp
and [#DraftInvoiceRecords].amo
and [#DraftInvoiceRecords].uni
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
My data has a sequential primary key so the first example provided by jimmym715 worked perfectly. Thank you very much jimmym715.
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?