mysql - 如何在Mysql中使用相同的查询删除3个表中的一行

Question

从 tbl_savedgroupmessage、tbl_savedusermessage、tbl_message 中删除 tbl_savedgroupmessage.msgid= tbl_savedusermessage.msgid= tbl_message.msgid= '876'

我试过了，但它抛出错误

Answer 1

试试这个 -

DELETE t1, t2, t3
FROM
  tbl_savedgroupmessage t1
JOIN tbl_savedusermessage t2
  ON t1.msgid = t2.msgid
JOIN tbl_message t3
  ON t1.msgid = t3.msgid
WHERE
  t3.msgid = '876'

示例:

CREATE TABLE table_a (
  id INT(11) DEFAULT NULL
);

CREATE TABLE table_b (
  id INT(11) DEFAULT NULL
);

INSERT INTO table_a VALUES 
  (2),
  (3),
  (1);

INSERT INTO table_b VALUES 
  (2),
  (5),
  (1);

DELETE t1, t2
FROM
  table_a t1
JOIN table_b t2
  ON t1.id = t2.id
WHERE
  t1.id = 1;


SELECT * FROM table_a;
+------+
| id   |
+------+
|    2 |
|    3 |
+------+

SELECT * FROM table_b;
+------+
| id   |
+------+
|    2 |
|    5 |
+------+