CODEWITHSUNDEEP
mysql
rkbom9
rkbom9

Reputation: 913

delete rows from 4 tables in one single sql

I am trying to delete rows from 4 tables in one single SQL. The tables have foreign keys of one another. The D1 table looks like

id     name     age
1      mike      12
2      john      43

the D2 table looks like

id     D1_id    price    salary
4        1        12       50
11       2        14       100

the A1 table looks like 

id     D1_id      text     date
 9       1         abc     12-dec  
188      1         xyz     12-dec
222      1         kya     12-dec
123      2         poq     13-dec

the C1 tables looks like 

id     A1_id      label     date
124      9         pqr      12-dec
145      9         qwe      12-dec
121     222        fdg      13-dec

The SQL that have tried till now 

delete FROM D1,D2,A1,C1 INNER JOIN D2 on (D1.id = D2.D1_id) where name = 'mike'

So I need to delete the rows based on the name from D1 table. I can change the name and it should it delete all corresponding rows from all tables.

Is it possible to use on delete cascade ?http://sqlfiddle.com/#!2/d5ed78/1

Upvotes: 0

Views: 153

Answers (1)

juergen d
juergen d

Reputation: 204756

delete d1, d2, a1, c1
from d1
left join d2 on d1.id = d2.d1_id
left join a1 on d1.id = a1.d1_id
left join c1 on a1.id = c1.a1_id
where d1.name = 'mike'

Upvotes: 1

Related Questions