MySQL，用一个查询更新多个表

Question

MySQL，用一个查询更新多个表我有一个更新三个表的函数，但是我使用三个查询来执行这个操作。我希望使用一种更方便的方法来进行良好做法。如何用一个查询更新MySQL中的多个表？

Answer 1

UPDATE t1

INNER JOIN t2 ON t2.t1_id = t1.id

INNER JOIN t3 ON t2.t3_id = t3.id

SET t1.a = 'something',

    t2.b = 42,

    t3.c = t2.c

WHERE t1.a = 'blah';

要查看要更新的内容，可以将其转换为SELECT语句，例如：

SELECT t2.t1_id, t2.t3_id, t1.a, t2.b, t2.c AS t2_c, t3.c AS t3_c

FROM t1

INNER JOIN t2 ON t2.t1_id = t1.id

INNER JOIN t3 ON t2.t3_id = t3.id

WHERE t1.a = 'blah';

使用与其他答案相同的表的示例：

SELECT Books.BookID, Orders.OrderID,

    Orders.Quantity AS CurrentQuantity,

    Orders.Quantity + 2 AS NewQuantity,

    Books.InStock AS CurrentStock,

    Books.InStock - 2 AS NewStock

FROM Books

INNER JOIN Orders ON Books.BookID = Orders.BookID

WHERE Orders.OrderID = 1002;

UPDATE Books

INNER JOIN Orders ON Books.BookID = Orders.BookID

SET Orders.Quantity = Orders.Quantity + 2,

    Books.InStock = Books.InStock - 2

WHERE Orders.OrderID = 1002;