DELETE from multiple tables

Squatrem · #1 (**permalink**) 11-19-04, 13:12

Dear forum,

After reading and re-re-re-reading the mysql documentation, I will once again have to submit my question to you

I would like to use the multi-table deletion to save a few queries.
I have tree tables

Code:

A(ID_A)   B(ID_A,ID_C)   C(ID_C)
---------------------------------
   0         0,0           0
   1         1,1           1
             1,2           2
             1,3           3

, and I would like to delete a record from A (1) and every records from C that are in relation through B with this record (the relations stored in B are automatically deleted with the innodb ON DELETE CASCADE).

I have tested the following :

Code:

DELETE FROM A, C 
USING A, B, C  
WHERE A.ID_A=1 
AND B.ID_A=A.ID_A 
AND C.ID_C=B.ID_C;

And the following :

Code:

DELETE FROM A, C 
USING A, C 
WHERE A.ID_A=1 
AND C.ID_C IN (SELECT ID_C FROM B WHERE ID_A=1);

Both A and C are referenced in B using a foreign key with an ON DELETE CASCADE statement.

In each of these cases, the record from A is deleted (OK), every relations with ID_A=1 are deleted from B (OK) but only one record from C is deleted (not OK).
Is there something I'm doing wrong ? Is this a timing issue ?

Thanks in advance for your help

Internet Services

Web Development

Digital Marketing

Consumer Tech