MySQL itself supports updating multiple tables with one update statement. Sometimes this is a very useful feature. Multiple-tablesyntaxUPDATE [LOW_PRIORITY] [IGNORE] table_referencesSETcol_name1 {expr1 | DEFAULT} [, col_name2 {expr2 | DEFAULT}] [WHEREwhere_condition] pre
MySQL itself supports updating multiple tables with one update statement. Sometimes this is a very useful feature. Multiple-table syntax UPDATE [LOW_PRIORITY] [IGNORE] table_references SET col_name1 = {expr1 | DEFAULT} [, col_name2 = {expr2 | DEFAULT}] [WHERE where_condition]/pre
MySQL itself supports updating multiple tables with one update statement. Sometimes this is a very useful feature.
Multiple-table syntax
UPDATE [LOW_PRIORITY] [IGNORE] table_references
SET col_name1 = {expr1 | DEFAULT} [, col_name2 = {expr2 | DEFAULT}]…
[WHERE where_condition]
Therefore, the description of table_references is continued;
Table_references:
Escaped_table_reference [, escaped_table_reference]…
Escaped_table_reference:
Table_reference
| {OJ table_reference}
Table_reference:
Table_factor
| Join_table
Table_factor:
Tbl_name [[AS] alias] [index_hint]
| Table_subquery [AS] alias
| (Table_references)
We can see that the keyword of update can be multiple tables, and each table can also be a subquery or a join statement.
A small attempt
In my other article, I have used this syntax:
UPDATE table_a, table_ B SET table_a.age = table_ B .age WHERE table_a.id = table_ B .id;
The table_ B table in this statement can also be replaced with subqueries and join clauses, for example:
UPDATE table_a, (SELECT id, age FROM table_ B) AS tb SET table_a.age = tb. age WHERE table_a.id = tb. id;
Mysql update official documentation: http://dev.mysql.com/doc/refman/5.0/en/update.html
Reprinted please indicate Source: Link
Original article address: MySQL updates multiple tables with one statement. Thank you for sharing it with the original author.