How does one MySQL statement update multiple tables?

We will use join subqueries to update multiple tables using one MySQL statement. Let's take a look at the implementation method.

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>

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:

The Code is as follows:	Copy code
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:

The Code is as follows:	Copy code
UPDATE table_a, (SELECT id, age FROM table_ B) AS tb SET table_a.age = tb. age WHERE table_a.id = tb. id;

If you don't understand it, let's look at another example.

The Code is as follows:

Copy code

Create table student ( Student_id int not null , Student_name varchar (30) not null , City_code varchar (10) null , City_name varchar (50) null ); Create table city ( Code varchar (10) not null , Name varchar (50) not null ); Insert into student values (1, 'john', '001', null ); Insert into student values (2, 'Nick ', '002', null ); Insert into city values ('001', 'beijing '); Insert into city values ('002 ', 'shanghai '); Insert into city values ('003 ', 'shenzhen ');

There are two tables: student & city. Now you need to retrieve the city. name to update student. city_name. The join conditions for two tables are:

The Code is as follows:	Copy code
Student. city_code = city. code. Update student s, city c Set s. city_name = c. name Where s. city_code = c. code;

You can also try the following subqueries:

The Code is as follows:	Copy code
Update student s set city_name = (select name from city where code = s. city_code );