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