Multiple delete in a single query

Question

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

Solution 1

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)

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

Comments

[Sharpeye500]

I want to delete Id value of 100 from the 3 tables. DELETE FROM Table1 WHERE ConditionID=100; DELETE FROM Table2 WHERE ConditionID=100; DELETE FROM Table3 WHERE ConditionID=100; instead of calling 3 times, is there in a single approach using some joins?

[Sharpeye500]

Excellent,outstanding approach.

[Luc M]

Not sur it's a good solution. If an id is not in table2 but the same id is in table1 and table3, will rows in table1 and table3 be deleted ?