mysql拼接sql更新_Postgresql，MySQL, SQL Server的多表连接（join）update操作

数据库更新时经常会join其他表做判断更新，PostgreSQL的写法与其他关系型数据库更有不同，下面以SQL Server, MySQL，PostgreSQL的数据库做对比和展示。

先造数据源。

create table A(id int, city varchar(20))；

create table B(id int, name varchar(20))；

insert into A values(1, 'beijing');

insert into A values(2, 'shanghai');

insert into A values(3, 'guangzhou');

insert into A values(4, 'hangzhou');

insert into B values(1, 'xiaoming');

insert into B values(2, 'xiaohong');

insert into B values(3, 'xiaolv');

现在我要将xiaohong的城市改为shenzhen，看看不同的数据库产品是怎么做的：

SQL Server:

update A

set A.city = 'shenzhen'

from A join B

on A.id = B.id

where B.name = 'xiaohong'

MySQL:

update A

join B ON A.id= B. id

set A.city='shenzhen'

where B.name = 'xiaohong'

PostgreSQL:

update A

set city = 'shenzhen'

from B

where A.id = B.id

and B.name = 'xiaohong'

需求更新：

如果要将 a表多余的id的city更新为 ‘abcd’, 即 4 -> ‘abcd’, 实现update left join

PostgreSQL

update a

set city = 'abcd'

from a a1

left join b

on a1.id = b.id

where a.id = a1.id

and b.id is null

如果要将a表中的city用b表中的那么更新， 即 1- >xiaoming, 2 -> xiaohong, 3 ->xiaolv

update a

set city = b.name

from a a1

join b

on a.id = b.id

where a1.id = a.id