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?
Let's assume two tables, Books and Orders. Now, if you want to increase the number of books in a particular order with Order.ID = 1002 in Orders table then you need to reduce the total number of books available in your stock by the same number in Books table like this:
UPDATE Books, OrdersSET Orders.Quantity = Orders.Quantity+2, Books.InStock = Books.InStock-2WHERE Books.BookID = Orders.BookID AND Orders.OrderID = 1002;
UPDATE Books, Orders
SET Orders.Quantity = Orders.Quantity+2,
Books.InStock = Books.InStock-2
Books.BookID = Orders.BookID
AND Orders.OrderID = 1002;