How does one MySQL statement update multiple tables?

Source: Internet
Author: User
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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.