mysql - 单个查询中的多个删除

Question

从 Table1 中删除 WHERE Con​​ditionID=?ConditionID;

DELETE FROM Table2 WHERE ConditionID=?ConditionID;

DELETE FROM Table3 WHERE ConditionID=?ConditionID;

ConditionID 是表 1、表 2、表 3 中的一个列，而不是单独运行 3 次，有没有办法在一个查询(在 mysql 中)中运行所有三个？

Answer 1

如果所有三个表的 ConditionID 都相同，您应该能够使用 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 = ?;

测试用例:

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

结果:

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)