CODEWITHSUNDEEP
mysqlforeign-keysprimary-keysql-delete
dhpratik
dhpratik

Reputation: 1138

Delete query referencing 2 other tables

I have 3 tables tab1, tab2 and tab3. in which the primary key of tab1 is foreign key to tab2 and primary key of tab 2 is foreign key to tab3

TAB1  |  TAB2     |  TAB3
-----------------------------
ID    |  ID       |  ID
      |  TAB1_ID  |  TAB2_ID

I want to delete a row in tab 3 in the following manner:

DELETE FROM TAB3 WHERE TAB2_ID=TAB2.ID AND TAB2.TAB1_ID=TAB1.ID

OR ELSE

DELETE FROM TAB3 WHERE TAB2_ID=TAB2.ID AND TAB2.TAB1_ID=3

Can anyone suggest a correct way to resolve this?

Upvotes: 0

Views: 210

Answers (2)

Jarek.D
Jarek.D

Reputation: 1294

Try this 

DELETE FROM tab3 
    JOIN tab2 ON tab3.tab2_id = tab2.id
    JOIN tab1 ON tab2.tab1_id = tab1.id

Upvotes: 0

eggyal
eggyal

Reputation: 125835

You can use the multiple-table DELETE syntax:

  1. DELETE FROM TAB3 WHERE TAB2_ID=TAB2.ID AND TAB2.TAB1_ID=TAB1.ID

    DELETE TAB3
FROM   TAB3
  JOIN TAB2 ON TAB2.ID = TAB3.TAB2_ID
  JOIN TAB1 ON TAB1.ID = TAB2.TAB1_ID

  2. DELETE FROM TAB3 WHERE TAB2_ID=TAB2.ID AND TAB2.TAB1_ID=3

    DELETE TAB3
FROM   TAB3
  JOIN TAB2 ON TAB2.ID = TAB3.TAB2_ID
WHERE  TAB2.TAB1_ID = 3

However, if you configure your foreign keys to CASCADE deletions, then MySQL will automatically delete child rows whenever the parent records are deleted (perhaps this is what you want?).

Upvotes: 1

Related Questions