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

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中的多个表?

6 个解决方案

#1

-16

You could do this with a stored procedure by combining the UPDATE statements in a single transaction.

通过在一个事务中组合UPDATE语句，您可以使用一个存储过程来实现这一点。

#2

362

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

以两张桌子为例，书和订单。在这种情况下，我们按一定的顺序增加书的数量。ID = 1002在订单表中，那么我们还需要将库存图书的总数减少到与图书表相同的数量。

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

#3

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")，对于对多个表更新的排序和限制也有一些限制。

#4

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

#5

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语句。使用回滚，您可以确保它们被视为一个工作单元，即要么全部执行，要么没有执行，以保持数据的一致性。

#6

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()调用执行所有三个查询。

#1

-16