Deleting rows from multiple tables

[guvenck]

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?

[StupidRalph]

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.)

[ralph l mayo]

Study up on InnoDB, foreign keys, and cascade:

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)

[StupidRalph]

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.

[ralph l mayo]

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:

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++

[guvenck]

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

[StupidRalph]

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

[guvenck]

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?

[Mwnciau]

Can't you just do?

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

[StupidRalph]

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

[guvenck]

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.

[StupidRalph]

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*]] ...
    FROM table_references
    [WHERE where_condition]

or

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

[StupidRalph]

Just successfully tested the following queries.

DELETE dir_subcat2,dir_subcat3 
FROM dir_subcat2,dir_subcat3 
WHERE 
tbl_subcat2.subCatID = 3 AND tbl_subcat3.subCatID = 3

Equivalent to

DELETE dir_subcat2,dir_subcat3 
FROM dir_subcat2,dir_subcat3 
WHERE 
tbl_subcat2.subCatID = tbl_subcat3.subCatID
AND
tbl_subcat2.subCatID = 3

[guvenck]

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()); 


[StupidRalph]

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?