原SQL

MySQL中update left join 测试语句

drop table aa;
drop table b;

create table aa(id int,name varchar(100));
create table b(id int,name varchar(100));

insert into aa values(1,'aaaaa');
insert into aa values(2,'aaaaa');
insert into b values(1,'bbbbb');

select * from aa;
id|name |
--+-----+
 1|aaaaa|
 2|aaaaa|
select * from b;
id|name |
--+-----+
 1|bbbbb|

--执行
update aa 
left join b on aa.id = b.id
set aa.name = b.name;
select * from aa;
id|name |
--+-----+
 1|bbbbb|
 2|     |

上述的结果是left join aa.id = b.id 匹配不到的结果将a的name设置为null了

MySQL update inner join 测试语句

drop table aa;
drop table b;

create table aa(id int,name varchar(100));
create table b(id int,name varchar(100));

insert into aa values(1,'aaaaa');
insert into aa values(2,'aaaaa');
insert into b values(1,'bbbbb');

select * from aa;
select * from b;

update aa 
inner join b on aa.id = b.id
set aa.name = b.name;
select * from aa;
id|name |
--+-----+
 1|bbbbb|
 2|aaaaa|

Oracle 和 PG都不支持上述SQL语句

Oracle 和 PostgreSQL MySQL都支持的SQL语句

SQL> UPDATE aa
  2  SET aa.name = (
  3      SELECT b.name
  4      FROM b
  5      WHERE aa.id = b.id
  6  );

2 rows updated.

SQL> select * from aa;

        ID NAME
---------- --------------------
         1 bbbbb
         2