Results 1 to 1 of 1
  1. #1
    Join Date
    Oct 2004
    Posts
    6

    Unhappy Unanswered: DELETE from multiple tables

    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
    Last edited by Squatrem; 11-19-04 at 14:16.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •