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

Question

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

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

如何用一个查询更新MySQL中的多个表？

Answer 1

UPDATE t1INNER JOIN t2 ON t2.t1_id = t1.idINNER JOIN t3 ON t2.t3_id = t3.idSET t1.a = 'something',    t2.b = 42,    t3.c = t2.cWHERE t1.a = 'blah';要查看要更新的内容，可以将其转换为SELECT语句，例如：SELECT t2.t1_id, t2.t3_id, t1.a, t2.b, t2.c AS t2_c, t3.c AS t3_cFROM t1INNER JOIN t2 ON t2.t1_id = t1.idINNER JOIN t3 ON t2.t3_id = t3.idWHERE 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 NewStockFROM BooksINNER JOIN Orders ON Books.BookID = Orders.BookIDWHERE Orders.OrderID = 1002;UPDATE BooksINNER JOIN Orders ON Books.BookID = Orders.BookIDSET Orders.Quantity = Orders.Quantity + 2,    Books.InStock = Books.InStock - 2WHERE Orders.OrderID = 1002;