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.

if required the delete in a single query use the ON DELETE CASCADE in table schema. refer http://stackoverflow.com/questions/6260688/how-do-i-use-cascade-delete-with-sql-server — Abdul Rasheed, May 05 '16 at 12:33
Please check my answer as accepted, you can do both deletions in one query. — Marcelo Agimóvel, Jul 13 '18 at 19:08

score 6 · Accepted Answer · answered May 05 '16 at 12:30

6

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)

answered May 05 '16 at 12:30

Jamiec

133,658
13
134
193

score 3 · Answer 2 · answered May 05 '16 at 12:33

3

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.

answered May 05 '16 at 12:33

wizzardmr42

1,634
12
22

Can you please provide syntax on CASCADE ON DELETE – SJMan May 05 '16 at 12:34
You set it up on the foreign key https://msdn.microsoft.com/en-GB/library/ms188066.aspx – wizzardmr42 May 05 '16 at 12:35

score 1 · Answer 3 · edited May 23 '17 at 12:15

1

You can't delete the records from multiple table from a single query. But you have two methods to solve this

Delete all the related records from child or mapping table, and then delete the Parent / header table record. (Multiple queries required here. Use SQL Transaction for a better control).
Or, Modify your foreign key constraint to ON DELETE CASCADE

edited May 23 '17 at 12:15

Community

1
1

answered May 05 '16 at 12:39

Abdul Rasheed

6,486
4
32
48

score -4 · Answer 4 · answered Jul 13 '18 at 19:07

-4

Yes, YOU CAN, I did it right now:

DELETE T1,T2 FROM T1
        INNER JOIN
    T2 ON T2.FIELD = T1.FIELD
WHERE
    T1.SOMETHING='SOMETHING'

answered Jul 13 '18 at 19:07

Marcelo Agimóvel

1,668
2
20
25

I can assure you this does not work on sql server and he is talking about mysql – giammin Jun 29 '20 at 09:08

Delete from 2 tables using INNER JOIN

4 Answers4