mysql多表关联更新（update join示例说明）

本文主要讲述在mysql数据库中，如何使用update join实现多表数据更新。

update join语法

update join SQL语法如下所示：

UPDATE T1, T2
SET T1.c2 = T2.c2,
    T1.c3 = T2.c3
WHERE T1.id = T2.id AND condition

或：

UPDATE T1 
INNER JOIN T2 ON T1.id = T2.id
SET T1.c2 = T2.c2
    T1.c3 = T2.c3
WHERE condition

update join示例

步骤一：准备工作-建表及数据初始化

CREATE TABLE person  (
  person_id int(11) NOT NULL,
  person_name varchar(30) NOT NULL,
  city_code varchar(50) NULL,
  city_name varchar(100)
);

CREATE TABLE city  (
  city_code varchar(50) NULL,
  city_name varchar(100) NOT NULL
);

INSERT INTO person VALUES(1000, '张三', '110101', NULL);
INSERT INTO person VALUES(1000, '李四', '110102', NULL);

INSERT INTO city VALUES('110101', '北京市东城区');
INSERT INTO city VALUES('110102', '北京市西城区');

步骤二：根据city表数据，初始化person表中的city_name字段值

如下所示：

UPDATE person p, city c 
SET p.city_name = c.city_name 
WHERE p.city_code = c.city_code

或：
UPDATE person p
INNER JOIN city c ON p.city_code = c.city_code 
SET p.city_name = c.city_name

--或使用以下的子查询
UPDATE person p 
SET city_name = ( SELECT c.city_name FROM city c WHERE c.city_code = p.city_code );

说明：如果需要更新多列，则只能使用表关联方式。