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]
INNER JOIN [#ReversedRecords]
ON [#DraftInvoiceRecords].employee = [#ReversedRecords].employee
and [#DraftInvoiceRecords].amount = [#ReversedRecords].amount
and [#DraftInvoiceRecords].units = [#ReversedRecords].units