Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Regular Coder
    Join Date
    Jan 2006
    Posts
    377
    Thanks
    8
    Thanked 1 Time in 1 Post

    Deleting rows from multiple tables

    Hello,

    I delete records from MySQL DB like this:

    Part 1:
    PHP Code:
    $result1 mysql_query("DELETE FROM blocks WHERE ID='$bid'") or die(mysql_error());
    if(
    mysql_affected_rows() == 1) {
        
    // block deleted

    However, I have other tables that include data related to this "block" and they also need to be deleted.

    Part 2:
    PHP Code:
    $result2 mysql_query("DELETE FROM blocks_modules WHERE bid='$bid'") or die(mysql_error());
    $result3 mysql_query("DELETE FROM blocks_pages WHERE bid='$bid'") or die(mysql_error());
    $result4 mysql_query("DELETE FROM permissions_blocks WHERE bid='$bid'") or die(mysql_error()); 
    Question:

    I can check if a row has been deleted using mysql_affected_rows in Part 1. But in Part 2, the number of rows is uncertain (unless I calculate it before deleting) and even a related data may not exist (and it may result an error). How can I check if these rows have been deleted as well?

  • #2
    Senior Coder
    Join Date
    Mar 2003
    Location
    Atlanta
    Posts
    1,037
    Thanks
    14
    Thanked 30 Times in 28 Posts
    Can't you just JOIN those tables together on bid and then run one DELETE query?

    (Probably would have been more useful if posted this in the MYSQL section.)
    Most of my questions/posts are fairly straightforward and simple. I post long verbose messages in an attempt to be thorough.

  • #3
    Regular Coder ralph l mayo's Avatar
    Join Date
    Nov 2005
    Posts
    951
    Thanks
    1
    Thanked 31 Times in 29 Posts
    Study up on InnoDB, foreign keys, and cascade:

    Code:
    mysql> create table a (id int not null primary key auto_increment) engine=innodb;
    Query OK, 0 rows affected (0.16 sec)
    
    mysql> create table b (a_id int not null, foreign key (a_id) references a(id) on delete cascade) engine=innodb;
    Query OK, 0 rows affected (0.16 sec)
    
    mysql> insert into a() values();
    Query OK, 1 row affected (0.00 sec)
    
    mysql> insert into a() values();
    Query OK, 1 row affected (0.01 sec)
    
    mysql> insert into a() values();
    Query OK, 1 row affected (0.08 sec)
    
    mysql> select * from a;
    +----+
    | id |
    +----+
    |  1 |
    |  2 |
    |  3 |
    +----+
    3 rows in set (0.00 sec)
    
    mysql> insert into b() values (1);
    Query OK, 1 row affected (0.01 sec)
    
    mysql> insert into b() values (2);
    Query OK, 1 row affected (0.01 sec)
    
    mysql> insert into b() values (3);
    Query OK, 1 row affected (0.01 sec)
    
    mysql> select * from b;
    +------+
    | a_id |
    +------+
    |    1 |
    |    2 |
    |    3 |
    +------+
    3 rows in set (0.00 sec)
    
    mysql> delete from a where id = 1;
    Query OK, 1 row affected (0.09 sec)
    
    mysql> select * from b;
    +------+
    | a_id |
    +------+
    |    2 |
    |    3 |
    +------+
    2 rows in set (0.00 sec)

  • #4
    Senior Coder
    Join Date
    Mar 2003
    Location
    Atlanta
    Posts
    1,037
    Thanks
    14
    Thanked 30 Times in 28 Posts
    Why oh why did I not think that MySQL support cascade deletion? I know I've read about this a few years ago but that was when I was starting PHP. Thanks, I've got some applications to optimize.
    Most of my questions/posts are fairly straightforward and simple. I post long verbose messages in an attempt to be thorough.

  • #5
    Regular Coder ralph l mayo's Avatar
    Join Date
    Nov 2005
    Posts
    951
    Thanks
    1
    Thanked 31 Times in 29 Posts
    Probably because it barely does. I had to try it a few times because it seemed like it didn't but I knew for a fact it could somehow. You've got to use InnoDB, and the syntax has got to look pretty much exactly like in my last post, judging from the version I've got installed (5.0.41-debian). Check this similar sequence that raises no warnings but completely fails to do what it's obviously been instructed to do:

    Code:
    mysql> create table a(id int not null primary key auto_increment) engine=innodb;
    Query OK, 0 rows affected (0.69 sec)
    
    mysql> create table b(a_id int not null references a(id) on delete cascade) engine=innodb;
    Query OK, 0 rows affected (0.72 sec)
    
    mysql> show warnings;
    Empty set (0.07 sec)
    
    // Snip inserts, exactly the same as my last post
    
    mysql> delete from a where id = 1;
    Query OK, 1 row affected (0.09 sec)
    
    mysql> show warnings;
    Empty set (0.00 sec)
    
    mysql> select * from b;
    +------+
    | a_id |
    +------+
    |    1 |
    |    2 |
    |    3 |
    +------+
    3 rows in set (0.00 sec)
    
    mysql> select * from a;
    +----+
    | id |
    +----+
    |  2 |
    |  3 |
    +----+
    2 rows in set (0.00 sec)
    Postgres++
    Last edited by ralph l mayo; 07-10-2007 at 12:27 PM.

  • #6
    Regular Coder
    Join Date
    Jan 2006
    Posts
    377
    Thanks
    8
    Thanked 1 Time in 1 Post


    So which one should I take for an answer? No other solution than InnoDB?

  • #7
    Senior Coder
    Join Date
    Mar 2003
    Location
    Atlanta
    Posts
    1,037
    Thanks
    14
    Thanked 30 Times in 28 Posts
    Thats dependent upon you as to which solution you pick...
    If you understand how the cascade deleting works in MySQL I'd suggest that.
    http://dev.mysql.com/doc/refman/5.0/...nstraints.html

    If you can't wrap your head around it. You can do as I suggested prior and use the JOIN statement to join the tables together and then run the DELETE query once on that one joined table. Here is a link courtesy of CodingForums own brilliant MySQL moderator Guelphdad that might help you on JOINS. (I'd bookmark this and some of these other pages if I were you. They've helped me a countless amount of times.)
    http://www.guelphdad.wefixtech.co.uk...ns_basic.shtml
    Most of my questions/posts are fairly straightforward and simple. I post long verbose messages in an attempt to be thorough.

  • #8
    Regular Coder
    Join Date
    Jan 2006
    Posts
    377
    Thanks
    8
    Thanked 1 Time in 1 Post
    I guess I'll go for the join method. But, the JOIN methods are mostly for SELECT queries, I guess. Is there any examples for the DELETE queries?

  • #9
    Regular Coder
    Join Date
    May 2006
    Location
    Wales
    Posts
    820
    Thanks
    1
    Thanked 82 Times in 79 Posts
    Can't you just do?

    Code:
    DELETE FROM table1, table2, table3 WHERE sid='$id';

  • #10
    Senior Coder
    Join Date
    Mar 2003
    Location
    Atlanta
    Posts
    1,037
    Thanks
    14
    Thanked 30 Times in 28 Posts
    You can use them for DELETE too. They have some examples in the comments section of the delete page.

    http://dev.mysql.com/doc/refman/5.0/en/delete.html
    Most of my questions/posts are fairly straightforward and simple. I post long verbose messages in an attempt to be thorough.

  • #11
    Regular Coder
    Join Date
    Jan 2006
    Posts
    377
    Thanks
    8
    Thanked 1 Time in 1 Post
    Tried this:

    PHP Code:
    $result mysql_query("
        DELETE FROM blocks b, blocks_modules bm, blocks_pages bp, permissions_blocks pb
        WHERE b.ID = bm.bid
        AND bm.bid = bp.bid
        AND bp.bid = pb.bid
        AND b.ID = '$bid'"
    ) or die(mysql_error()); 
    It does not work. I am getting this nice error message:

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE b.ID = bm.bid AND bm.bid = bp.bid AND bp.bid = pb.bid AND b.ID = '12' at line 2

    My MySQL version is 4.1, so deleting from multiple tables is supported.

  • #12
    Senior Coder
    Join Date
    Mar 2003
    Location
    Atlanta
    Posts
    1,037
    Thanks
    14
    Thanked 30 Times in 28 Posts
    Code:
    DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
        tbl_name[.*] [, tbl_name[.*]] ...
        FROM table_references
        [WHERE where_condition]
    or
    Code:
    DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
        FROM tbl_name[.*] [, tbl_name[.*]] ...
        USING table_references
        [WHERE where_condition]
    http://dev.mysql.com/doc/refman/5.0/en/delete.html
    Last edited by StupidRalph; 07-10-2007 at 08:07 PM. Reason: Deleted an example
    Most of my questions/posts are fairly straightforward and simple. I post long verbose messages in an attempt to be thorough.

  • #13
    Senior Coder
    Join Date
    Mar 2003
    Location
    Atlanta
    Posts
    1,037
    Thanks
    14
    Thanked 30 Times in 28 Posts
    Just successfully tested the following queries.
    Code:
    DELETE dir_subcat2,dir_subcat3 
    FROM dir_subcat2,dir_subcat3 
    WHERE 
    tbl_subcat2.subCatID = 3 AND tbl_subcat3.subCatID = 3
    Equivalent to

    Code:
    DELETE dir_subcat2,dir_subcat3 
    FROM dir_subcat2,dir_subcat3 
    WHERE 
    tbl_subcat2.subCatID = tbl_subcat3.subCatID
    AND
    tbl_subcat2.subCatID = 3
    Last edited by StupidRalph; 07-10-2007 at 08:08 PM. Reason: added another version of the SQL statement
    Most of my questions/posts are fairly straightforward and simple. I post long verbose messages in an attempt to be thorough.

  • #14
    Regular Coder
    Join Date
    Jan 2006
    Posts
    377
    Thanks
    8
    Thanked 1 Time in 1 Post
    After trying several alternatives, look which one appears to be working:

    EDIT: No, it does not work as expected. It doesn't delete anything if there is no matching data in one of the tables ( where bid=$bid ). It looks like it requires at least one row with bid=$bid in each of the tables.

    PHP Code:
    $result mysql_query("
        DELETE blocks, blocks_modules, blocks_pages_static, permissions_blocks
        FROM blocks
        INNER JOIN blocks_modules
        ON blocks.ID = blocks_modules.bid
        INNER JOIN blocks_pages_static
        ON blocks_modules.bid = blocks_pages_static.bid
        INNER JOIN permissions_blocks
        ON blocks_pages_static.bid = permissions_blocks.bid
        WHERE blocks.ID = '$bid'
    "
    ) or die(mysql_error()); 
    Last edited by guvenck; 07-10-2007 at 08:57 PM. Reason: I am mistaken :)

  • #15
    Senior Coder
    Join Date
    Mar 2003
    Location
    Atlanta
    Posts
    1,037
    Thanks
    14
    Thanked 30 Times in 28 Posts
    Hmmm maybe we should continue this in the MySQL forum and perhaps let the guys over there assist us. What table type is that? Is it an InnoDB?
    Most of my questions/posts are fairly straightforward and simple. I post long verbose messages in an attempt to be thorough.


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

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