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

Question

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

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

Answer 1

以两个表Books和Orders为例。如果我们在Orders表中使用Order.ID = 1002以特定的顺序增加图书数量，那么我们还需要在Books表中减少库存中可用图书的总数。

UPDATE Books, Orders
SET Orders.Quantity = Orders.Quantity + 2,
    Books.InStock = Books.InStock - 2
WHERE
    Books.BookID = Orders.BookID
    AND Orders.OrderID = 1002;

Answer 2

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;

编辑：

只是为了好玩，让我们添加一些更有趣的东西。

假设您有一张books表和一张authors表。您的books有一个author_id。但是在最初创建数据库时，没有设置外键约束，后来前端代码中的一个bug导致添加了一些带有无效author_id的书籍。作为一名数据库管理员，您不希望必须遍历所有这些books来检查author_id应该是什么，所以决定数据捕获器将books修复为指向正确的authors。但是有太多的书不能一一浏览，假设您知道那些具有与实际author相对应的author_id的书是正确的。只有那些不存在的author_id才是无效的。用户已经有了一个更新图书详细信息的界面，开发人员不想仅仅为了这个问题而改变它。但是现有的接口做了一个INNER JOIN authors，所以所有无效作者的图书都被排除在外。

你能做的是:插入一个假的作者记录，比如"Unknown author“。然后更新所有坏记录的author_id以指向未知作者。然后，数据捕获器可以搜索作者设置为“未知作者”的所有书籍，查找正确的作者并修复它们。

如何更新所有的坏记录以指向未知的作者？如下所示(假设未知作者的author_id为99999)：

UPDATE books
LEFT OUTER JOIN authors ON books.author_id = authors.id
SET books.author_id = 99999
WHERE authors.id IS NULL;

上面的代码还将更新具有指向未知作者的NULL author_id的books。如果您不想这样做，当然可以添加AND books.author_id IS NOT NULL。

Answer 3

您也可以使用一个查询来实现这一点，如下所示：

UPDATE table1,table2 SET table1.col=a,table2.col2=b
WHERE items.id=month.id;

当然，然后只发送一个查询。你可以在这里阅读更多关于连接的内容：http://dev.mysql.com/doc/refman/5.0/en/join.html。您可以在这里了解到对多个表更新的排序和限制的一些限制：http://dev.mysql.com/doc/refman/5.0/en/update.html (只需ctrl+f "join")。