Multiple delete in a single query.

Question

Hi,

DELETE FROM Table1 WHERE ConditionID=?ConditionID;

DELETE FROM Table2 WHERE ConditionID=?ConditionID;

DELETE FROM Table3 WHERE ConditionID=?ConditionID;

ConditionID is a column present in Table1,Table2,Table3, instead of running 3 times individually, is there a way to run all the three in a single query(in mysql)?

Thanks.

Answer 1

Nope. ConditionID is separate in each one—just because it has the same name doesn't mean it's the same column. If you fully qualify the names, you'll be able to see it better:

DELETE FROM Table1 WHERE Table1.ConditionID=?ConditionID;

DELETE FROM Table2 WHERE Table2.ConditionID=?ConditionID;

DELETE FROM Table3 WHERE Table3.ConditionID=?ConditionID;

Answer 2

I don't know what your schema looks like, but if you use InnoDB, or a similar table engine for your tables and you have foreign keys, you can set conditions that will cause derived entries to be deleted when a parent entry is deleted. See http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html for more info on that.

Answer 3

If the ConditionID is the same for all the three tables, you should be able to use the Multiple Table Delete Syntax:

DELETE Table1, Table2, Table3
FROM   Table1
JOIN   Table2 ON (Table2.ConditionID = Table1.ConditionID)
JOIN   Table3 ON (Table3.ConditionID = Table2.ConditionID)
WHERE  Table1.ConditionID = ?;

Test case:

CREATE TABLE Table1 (id int, ConditionID int);
CREATE TABLE Table2 (id int, ConditionID int);
CREATE TABLE Table3 (id int, ConditionID int);

INSERT INTO Table1 VALUES (1, 100);
INSERT INTO Table1 VALUES (2, 100);
INSERT INTO Table1 VALUES (3, 200);

INSERT INTO Table2 VALUES (1, 100);
INSERT INTO Table2 VALUES (2, 200);
INSERT INTO Table2 VALUES (3, 300);

INSERT INTO Table3 VALUES (1, 100);
INSERT INTO Table3 VALUES (2, 100);
INSERT INTO Table3 VALUES (3, 100);

Result:

DELETE Table1, Table2, Table3
FROM   Table1
JOIN   Table2 ON (Table2.ConditionID = Table1.ConditionID)
JOIN   Table3 ON (Table3.ConditionID = Table2.ConditionID)
WHERE  Table1.ConditionID = 100;

SELECT * FROM Table1;
+------+-------------+
| id   | ConditionID |
+------+-------------+
|    3 |         200 |
+------+-------------+
1 row in set (0.00 sec)

SELECT * FROM Table2;
+------+-------------+
| id   | ConditionID |
+------+-------------+
|    2 |         200 |
|    3 |         300 |
+------+-------------+
2 rows in set (0.00 sec)

SELECT * FROM Table3;
Empty set (0.00 sec)