Delete from 2 tables using INNER JOIN

Question

I have 3 tables.

• InvoiceOriginal
• Invoice
• InvoiceHistory

the invoice table has a foreign key constraint. Each entry in the invoice table has a corresponding entry in Invoiceoriginal.

The invoiceOriginal table stores the original values of the invoice and invoice table stores the values which have been modified by the user. this is done to get diferrences at the time of submission.

the SQL I am using is

DELETE i
FROM invoice i
INNER JOIN InvoiceHistory aih
   ON i.ClientId = aih.HistoryClientNumber
   AND i.invoiceNumber = HistoryInvoiceNumber

however deletion is not possible because of foreign Key constraint.

The table is as under:

Invoice         InvoiceOriginal         InvoiceHistory
 Id                FK_InvoiceId            ClientId
 ClientId          ClientId                InvoiceNumber
 InvoiceNumber

I need to delete the entry in invoice and InvoiceOriginal once there is an entry for that invoice number in InvoiceHistory for the same clientId.

Answer 1

You cannot issue a delete statement against more than one table at a time, you need to have individual delete statements for each of the related tables before deleting the parent record(s)

Answer 2

I'm fairly sure you can't delete from multiple tables with a single statement. I would normally delete the child rows first with one statement and then delete the parent record. You may wish to do this inside a transaction if you might need to roll back on failure.

Alternatively, you could enable CASCADE ON DELETE on the foreign key which would automatically cascade the deletions through the child records if that is something that is suitable for this system.