用一个表中的字段去更新另外一个表中的字段, MySQL 中有相应的 update 语句来支持,不过这个 update 语法有些特殊。看一个例子就明白了。
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' );
update student
set student.city_name = city.name
from student
inner join city
on (student.city_code = city.code);
update student s set city_name = (select name from city where code = s.city_code);