MySQL A statement to update multiple tables method

Source: Internet
Author: User

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

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.