sql - MySQL, update multiple tables with one query

Question

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?

Answer 1

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;