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 stackoverflow.com/questions/6260688/… — Abdul Rasheed
– Abdul Rasheed, Commented May 5, 2016 at 12:33
Please check my answer as accepted, you can do both deletions in one query. — Marcelo Agimóvel
– Marcelo Agimóvel, Commented Jul 13, 2018 at 19:08

Jamiec · Accepted Answer · 2016-05-05 12:30:52Z

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 5, 2016 at 12:30

Jamiec

136k15 gold badges141 silver badges202 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

wizzardmr42 · Accepted Answer · 2016-05-05 12:33:03Z

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 5, 2016 at 12:33

wizzardmr42

1,65412 silver badges25 bronze badges

2 Comments

SJMan Over a year ago

Can you please provide syntax on CASCADE ON DELETE

wizzardmr42 Over a year ago

You set it up on the foreign key msdn.microsoft.com/en-GB/library/ms188066.aspx

Community · Accepted Answer · 2017-05-23 12:15:54Z

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, 2017 at 12:15

CommunityBot

11 silver badge

answered May 5, 2016 at 12:39

Abdul Rasheed

6,7794 gold badges36 silver badges50 bronze badges

Comments

Marcelo Agimóvel · Accepted Answer · 2018-07-13 19:07:23Z

-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, 2018 at 19:07

Marcelo Agimóvel

1,7192 gold badges23 silver badges27 bronze badges

1 Comment

giammin Over a year ago

I can assure you this does not work on sql server and he is talking about mysql

Collectives™ on Stack Overflow

Delete from 2 tables using INNER JOIN

4 Answers 4

Comments

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related