How does one MySQL statement update multiple tables?

Source: Internet
Author: User

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 );

Related Article

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.