As you well know, multiple server hits can slow down an application. For that reason, developers are keen to find the most efficient ways to update data using as few statements as possible. As it turns out, the SQL UPDATE statement does support the setting of fields from multiple tables using this syntax:
UPDATE table1, table2, ...
SET column1 = value1,
column2 = value2,
...
[WHERE conditions]
The syntax gets formed by the combination of various keywords that helps in the grouping of two or more tables, like the join keyword.
Today's blog will present an overview of the multi-table UPDATE statement along with an example using MySQL 8 and Navicat Premium 16.
Some Caveats
Combining two table updates into one statement is not without limitations and quirks. Here are some points to keep in mind:
- In the multi-table UPDATE query, each record satisfying a condition gets updated. Even if the criteria are matched multiple times, the row is updated only once.
- The syntax of updating multiple tables cannot be used with the ORDER BY and LIMIT keywords.
So, while the multi-table UPDATE statement is quite efficient, it is not ideal for every situation.
A Practical Example
To give the multi-table UPDATE statement a try, we'll create two tables named "library" and "book" and consider the case when one or more books are borrowed from the library. Doing so increases the count of books while decreasing the count of the books. As it turns out, that's the ideal scenario to combine two separate statements into one UPDATE query. This will avoid separate calls to the server, making it a very efficient operation.
Here are the definitions and contents of each table:
The library Table
The book Table
Here is the query that will update both tables:
UPDATE library l, book b
SET l.book_count = l.book_count - 2,
b.book_count = b.book_count + 2
WHERE l.id = b.book_id
AND b.id = '1AG';
In the above query, the l.id = b.book_id condition acts as an inner join which combines the two tables and operates on the combined table after checking the table constraints. Meanwhile, the b.id = '1AG' condition further reduces the target rows to those which pertain to user '1AG'.
Other join types like outer join and right outer join may be employed as well; the only mitigating factor is that the two tables getting grouped must have a similar/matching attribute.
As with the regular (single table) UPDATE statement, the SET keyword is used along with the UPDATE keyword to set the new values in existing rows. It causes the older values to be overwritten with the new data. We can observe the query results in Navicat below:
As expected, the book counts for user '1AG' and book 103 have been updated in both tables:
Conclusion
Today's blog presented an overview of the multi-table UPDATE statement along with an example using MySQL 8 and Navicat Premium 16. The lesson here is that the multi-table UPDATE statement works best for applying mathematical operations such as incrementing and decrementing on related table columns.
