...

View Full Version : Deleting rows from multiple tables



guvenck
07-09-2007, 11:49 PM
Hello,

I delete records from MySQL DB like this:

Part 1:


$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:


$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
07-10-2007, 07:34 AM
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
07-10-2007, 08:33 AM
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
07-10-2007, 09:28 AM
:eek: 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
07-10-2007, 11:18 AM
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
07-10-2007, 03:08 PM
:)

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

StupidRalph
07-10-2007, 04:48 PM
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/en/innodb-foreign-key-constraints.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/sqlhelp/joins_basic.shtml

guvenck
07-10-2007, 05:00 PM
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
07-10-2007, 05:36 PM
Can't you just do?


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

StupidRalph
07-10-2007, 05:36 PM
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
07-10-2007, 05:57 PM
Tried this:



$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
07-10-2007, 06:25 PM
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
07-10-2007, 06:36 PM
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
07-10-2007, 07:42 PM
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.



$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
07-12-2007, 11:28 AM
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?

guvenck
07-12-2007, 03:30 PM
Should we ask an admin to move the topic to MySQL section? It is a typical MyISAM.

Here I post a structure and some example data. The table 4k_permissions_blocks has no data for block 1, I made it on purpose so we can test if our single query will work.



CREATE TABLE `4k_blocks` (
`ID` int(11) NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=3;

INSERT INTO `4k_blocks` VALUES (1, 'Test Block 1');
INSERT INTO `4k_blocks` VALUES (2, 'Test Block 2');


CREATE TABLE `4k_blocks_modules` (
`bid` int(11) NOT NULL default '0',
`pid` int(11) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


INSERT INTO `4k_blocks_modules` VALUES (1, 3);
INSERT INTO `4k_blocks_modules` VALUES (1, 2);
INSERT INTO `4k_blocks_modules` VALUES (1, 1);
INSERT INTO `4k_blocks_modules` VALUES (2, 3);
INSERT INTO `4k_blocks_modules` VALUES (2, 2);


CREATE TABLE `4k_blocks_pages_static` (
`bid` int(11) NOT NULL default '0',
`pid` int(11) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `4k_blocks_pages_static` VALUES (1, 7);
INSERT INTO `4k_blocks_pages_static` VALUES (1, 6);
INSERT INTO `4k_blocks_pages_static` VALUES (1, 5);
INSERT INTO `4k_blocks_pages_static` VALUES (1, 4);
INSERT INTO `4k_blocks_pages_static` VALUES (2, 3);
INSERT INTO `4k_blocks_pages_static` VALUES (2, 2);
INSERT INTO `4k_blocks_pages_static` VALUES (2, 1);


CREATE TABLE `4k_permissions_blocks` (
`bid` int(11) NOT NULL default '0',
`profile` int(11) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `4k_permissions_blocks` VALUES (2, 100);
INSERT INTO `4k_permissions_blocks` VALUES (2, 10);
INSERT INTO `4k_permissions_blocks` VALUES (2, 1);



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum