mysql update inner join错误

Answer 1

create table tb1(
  country int,
  province int,
  city int
);

create table tb2(
  country int,
  province int,
  city int
);

insert into tb1 (country, province, city) values (1, 2, 5), (1, 3, null);
insert into tb2 (country, province, city) values (1, 2, 5), (1, 3, 7);







select * from tb1;

select * from tb2;

update tb1
inner join tb2 on tb1.country=tb2.country and tb1.province=tb2.province
set tb1.city=tb2.city
where tb1.city is null; -- and tb1.xx=?

select * from tb1;

select * from tb2;

update from 语句在 mssql 中有效, mysql 似乎无法正常执行

update tb1 set city=r.city from (
select country, province, city from tb2
) as r
where tb1.city is null and tb1.country=r.country and tb1.province=r.province

http://sqlfiddle.com/#!9/4df7d7/2