MySQL A statement to update multiple tables method

MySQL itself supports an UPDATE statement to update multiple tables, which is sometimes 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>

So continue to find table_references description;

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)

As you can see, the keyword of update can write multiple tables, each table can be a subquery, or it can be a join statement.

A little Try

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, join clauses, such as:

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, let's take another look at an example to see.

The code is as follows

Copy Code

CREATE TABLE Student ( student_id int NOT NULL , Student_name varchar () NOT NULL , City_code varchar (a) NULL , City_name varchar (m) NULL ); CREATE TABLE City ( Code varchar (TEN) NOT NULL , name varchar NOT NULL ); INSERT into student values (1, ' John ', ' 001 ', NULL); INSERT into student values (2, ' Nick ', ' 002 ', NULL); Insert into the city values (' 001 ', ' Beijing '); Insert into the city values (' 002 ', ' Shanghai '); Insert into the city values (' 003 ', ' Shenzhen ');

There are two tables: Student & City, now need to remove City.name to update student.city_name. The two-table association condition is

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 correlated subqueries:

code is as follows	copy code
Update Student S Set city_name = (select name from city where code = S.city_code);