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