0 votes
1 view
in SQL by (20.3k points)

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?

1 Answer

0 votes
by (40.4k points)

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, Orders

SET Orders.Quantity = Orders.Quantity+2,

    Books.InStock = Books.InStock-2

WHERE

    Books.BookID = Orders.BookID

    AND Orders.OrderID = 1002;

Related questions

0 votes
1 answer
0 votes
1 answer
asked Jul 18, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
asked Jul 4, 2019 in SQL by Tech4ever (20.3k points)
Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...