Example
In multiple table UPDATE, it updates rows in each specified tables that satisfy the conditions. Each matching row is updated once, even if it matches the conditions multiple times.
In multiple table UPDATE, ORDER BY and LIMIT cannot be used.
Syntax for multi table UPDATE is,
UPDATE [LOW_PRIORITY] [IGNORE]
table1, table2, ...
SET column1 = expression1,
column2 = expression2,
...
[WHERE conditions]
For example consider two tables, products and salesOrders. In case, we decrease the quantity of a particular product from the sales order which is placed already. Then we also need to increase that quantity in our stock column of products table. This can be done in single SQL update statement like below.
UPDATE products, salesOrders
SET salesOrders.Quantity = salesOrders.Quantity - 5,
products.availableStock = products.availableStock + 5
WHERE products.productId = salesOrders.productId
AND salesOrders.orderId = 100 AND salesOrders.productId = 20;
In the above example, quantity '5' will be reduced from the salesOrders table and the same will be increased in products table according to the WHERE conditions.
