--pg支持 update from 语法
postgres=# create table t1 as select n id,'rudy'||n as name from generate_series(1,3) n;
SELECT 10
postgres=# select * from t1;
id | name
----+--------
1 | rudy1
2 | rudy2
3 | rudy3
(3 rows)
--创建表
postgres=# create table t2 as select * from t1 where 1=0;
SELECT 0
postgres=# insert into t2 values(1,'rudy'),(1,'rudy2'),(1,'rudy3');
INSERT 0 3
postgres=# select * from t1,t2 where t1.id=t2.id;
id | name | id | name
----+-------+----+-------
1 | rudy1 | 1 | rudy
1 | rudy1 | 1 | rudy2
1 | rudy1 | 1 | rudy3
(3 rows)
--注意此时的 update from 类似于对t1表做了distint操作，故只更新一条
postgres=# update t1 set name=t2.name from t2 where t1.id=t2.id;
UPDATE 1
postgres=# select distinct t1.id,t1.name from t1,t2 where t1.id=t2.id;
id | name
----+------
1 | rudy
