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屋！