Removing from 2 tables with INNER JOIN

Question

I have 3 tables.

• InvoiceOriginal
• Invoice
• InvoiceHistory

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

The invoiceOriginal table stores the original invoice values, and the invoice table stores the values ​​that have been changed by the user. this is done to get differences during filing.

The SQL I use is

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

however, deletion is not possible due to a foreign key constraint.

The table looks like this:

 Invoice InvoiceOriginal InvoiceHistory Id FK_InvoiceId ClientId ClientId ClientId InvoiceNumber InvoiceNumber 

I need to delete the invoice entry and InvoiceOriginal as soon as the number for this account number is indicated in InvoiceHistory for the same clientId.

Answer 1

You cannot issue a delete statement for more than one table at a time, before deleting the parent record (s)

You must have separate deletion instructions for each of the related tables.

Answer 2

I am sure that you cannot delete from multiple tables with a single statement. Usually I delete the child strings with a single statement, and then delete the parent record. You might want to do this inside a transaction if you might need to roll back on failure.

Alternatively, you can enable CASCADE ON DELETE for a foreign key that automatically cascades deletes through child records, if that is what is appropriate for this system.

Answer 3

You cannot delete records from multiple tables from a single query. But you have two ways to solve this problem.

• Delete all related records from the child table or mapping table, and then delete the parent / header table entry. (Requires multiple queries. Use SQL Transaction for better control).

• Or change the foreign key constraint to ON DELETE CASCADE