MySQL，一次查询更新多个表 [英] MySQL, update multiple tables with one query

问题描述

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

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.

如何在 MySQL 中使用单个查询更新多个表?

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

推荐答案

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

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.

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

这篇关于MySQL，一次查询更新多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！