MySQL，一次查询更新多张表

MySQL, update multiple tables with one query

提问by Adamski

I have a function that updates three tables, but I use three queries to perform this. I wish to use a more convenient approach for good practice.

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

How can I update multiple tables in MySQL with a single query?

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

回答by Irfan

Take the case of two tables, Booksand Orders. In case, we increase the number of books in a particular order with Order.ID = 1002in Orderstable then we also need to reduce that the total number of books available in our stock by the same number in Bookstable.

以两张表为例，Books和Orders。在情况下，我们与增加特定顺序的图书数量Order.ID = 1002的Orders表，然后我们还需要减少由在相同数量在我们的股票提供书籍总数Books表。

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

回答by Wodin

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

To see what this is going to update, you can convert this into a select statement, e.g.:

要查看将要更新的内容，您可以将其转换为 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';

An example using the same tables as the other answer:

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

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;

EDIT:

编辑：

Just for fun, let's add something a bit more interesting.

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

Let's say you have a table of booksand a table of authors. Your bookshave an author_id. But when the database was originally created, no foreign key constraints were set up and later a bug in the front-end code caused some books to be added with invalid author_ids. As a DBA you don't want to have to go through all of these booksto check what the author_idshould be, so the decision is made that the data capturers will fix the booksto point to the right authors. But there are too many books to go through each one and let's say you know that the ones that have an author_idthat corresponds with an actual authorare correct. It's just the ones that have nonexistent author_ids that are invalid. There is already an interface for the users to update the book details and the developers don't want to change that just for this problem. But the existing interface does an INNER JOIN authors, so all of the books with invalid authors are excluded.

假设您有一张表books和一张 表authors。你books有一个author_id. 但是最初创建数据库的时候，并没有设置外键约束，后来前端代码的一个bug导致一些书籍添加了无效的author_ids。作为一名 DBA，您不希望通过所有这些books来检查author_id应该是什么，因此决定数据捕获器将修复booksto 指向正确的authors。但是有太多的书要逐一阅读，假设您知道具有author_id与实际相对应的那些书author是正确的。只是那些不存在的author_ids 无效。已经有一个界面供用户更新书籍详细信息，开发人员不想仅仅为了这个问题而改变它。但是现有的界面做了一个INNER JOIN authors，所以所有作者无效的书都被排除在外。

What you can do is this: Insert a fake author record like "Unknown author". Then update the author_idof all the bad records to point to the Unknown author. Then the data capturers can search for all books with the author set to "Unknown author", look up the correct author and fix them.

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

How do you update all of the bad records to point to the Unknown author? Like this (assuming the Unknown author's author_idis 99999):

您如何更新所有不良记录以指向未知作者？像这样（假设未知作者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;

The above will also update booksthat have a NULLauthor_idto the Unknown author. If you don't want that, of course you can add AND books.author_id IS NOT NULL.

以上也将更新对未知作者books有一个NULLauthor_id。如果你不想那样，当然你可以添加AND books.author_id IS NOT NULL.

回答by Stephen Searles

You can also do this with one query too using a join like so:

您也可以使用这样的连接通过一个查询来执行此操作：

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

And then just send this one query, of course. You can read more about joins here: http://dev.mysql.com/doc/refman/5.0/en/join.html. There's also a couple restrictions for ordering and limiting on multiple table updates you can read about here: http://dev.mysql.com/doc/refman/5.0/en/update.html(just ctrl+f "join").

当然，然后只需发送这个查询。您可以在此处阅读有关连接的更多信息：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”）。

回答by code_burgar

When you say multiple queries do you mean multiple SQL statements as in:

当您说多个查询时，您的意思是多个 SQL 语句，例如：

UPDATE table1 SET a=b WHERE c;
UPDATE table2 SET a=b WHERE d;
UPDATE table3 SET a=b WHERE e;

Or multiple query function calls as in:

或多个查询函数调用，如：

mySqlQuery(UPDATE table1 SET a=b WHERE c;)
mySqlQuery(UPDATE table2 SET a=b WHERE d;)
mySqlQuery(UPDATE table3 SET a=b WHERE e;)

The former can all be done using a single mySqlQuery call if that is what you wanted to achieve, simply call the mySqlQuery function in the following manner:

前者可以使用单个 mySqlQuery 调用完成，如果这是您想要实现的，只需按以下方式调用 mySqlQuery 函数：

mySqlQuery(UPDATE table1 SET a=b WHERE c; UPDATE table2 SET a=b WHERE d; UPDATE table3 SET a=b WHERE e;)

This will execute all three queries with one mySqlQuery() call.

这将通过一次 mySqlQuery() 调用执行所有三个查询。

回答by SteveCav

That's usually what stored procedures are for: to implement several SQL statements in a sequence. Using rollbacks, you can ensure that they are treated as one unit of work, ie either they are all executed or none of them are, to keep data consistent.

这通常是存储过程的用途：在一个序列中实现多个 SQL 语句。使用回滚，您可以确保将它们视为一个工作单元，即它们要么全部执行，要么都不执行，以保持数据一致。