开发说使用多表关联进行更新的时候发现没有正确的更新记录（事后发现是条件问题），之前一直没怎么使用关联更新，这次看了下，发现了个问题，当返回多行记录的时候并不会像传统的子查询更新那样报错，而是随机选择一个记录进行更新（ 貌似最后一个？ ） 因此
开发说使用多表关联进行更新的时候发现没有正确的更新记录（事后发现是条件问题），之前一直没怎么使用关联更新，这次看了下，发现了个问题，当返回多行记录的时候并不会像传统的子查询更新那样报错，而是随机选择一个记录进行更新（貌似最后一个？）
因此，虽然关联更新会快很多，但是要考虑的这个不确定带来的隐患。
gtlions=# create table joinupdate1(id int,name varchar(20));notice: table doesn't have 'distributed by' clause -- using column named 'id' as the greenplum database data distribution key for this table.hint: the 'distributed by' clause determines the distribution of data. make sure column(s) chosen are the optimal data distribution key to minimize skew.create tablegtlions=# create table joinupdate2(id int,name varchar(20));notice: table doesn't have 'distributed by' clause -- using column named 'id' as the greenplum database data distribution key for this table.hint: the 'distributed by' clause determines the distribution of data. make sure column(s) chosen are the optimal data distribution key to minimize skew.create tablegtlions=# insert into joinupdate1 values(1,'a');insert 0 1gtlions=# insert into joinupdate1 values(2,'b');insert 0 1gtlions=# insert into joinupdate2 values(1,'b');insert 0 1gtlions=# insert into joinupdate2 values(2,'b');insert 0 1gtlions=# truncate table joinupdate2;truncate tablegtlions=# insert into joinupdate2 values(1,'c');insert 0 1gtlions=# insert into joinupdate2 values(2,'d'); insert 0 1gtlions=# select * from joinupdate1; id | name ----+------ 1 | a 2 | b(2 rows)gtlions=# select * from joinupdate2; id | name ----+------ 1 | c 2 | d(2 rows)gtlions=# begin;begingtlions=# update joinupdate1 set name=(select name from joinupdate2 where joinupdate1.id=joinupdate2.id);update 2gtlions=# select * from joinupdate1; id | name ----+------ 1 | c 2 | d(2 rows)gtlions=# rollback;rollbackgtlions=# end;warning: there is no transaction in progresscommitgtlions=# select * from joinupdate1; id | name ----+------ 1 | a 2 | b(2 rows)gtlions=# begin;begingtlions=# update joinupdate1 set name=joinupdate2.name from joinupdate2 where joinupdate1.id=joinupdate2.id;update 2gtlions=# select * from joinupdate1; id | name ----+------ 1 | c 2 | d(2 rows)gtlions=# rollback;rollbackgtlions=# insert into joinupdate2 values(1,'e');insert 0 1gtlions=# begin;begingtlions=# update joinupdate1 set name=(select name from joinupdate2 where joinupdate1.id=joinupdate2.id);error: more than one row returned by a subquery used as an expression (seg0 slice2 h1:40000 pid=14123)gtlions=# rollback;rollbackgtlions=# begin;begingtlions=# update joinupdate1 set name=joinupdate2.name from joinupdate2 where joinupdate1.id=joinupdate2.id;update 2gtlions=# select * from joinupdate1; id | name ----+------ 1 | e 2 | d(2 rows)gtlions=# update joinupdate1 set name=joinupdate2.name from joinupdate2 where joinupdate1.id=joinupdate2.id;update 2gtlions=# select * from joinupdate1; id | name ----+------ 1 | e 2 | d(2 rows)gtlions=# update joinupdate1 set name=joinupdate2.name from joinupdate2 where joinupdate1.id=joinupdate2.id;update 2gtlions=# select * from joinupdate1; id | name ----+------ 1 | e 2 | d(2 rows)gtlions=# insert into joinupdate2 values(1,'f');insert 0 1gtlions=# update joinupdate1 set name=joinupdate2.name from joinupdate2 where joinupdate1.id=joinupdate2.id;update 2gtlions=# select * from joinupdate1; id | name ----+------ 1 | f 2 | d(2 rows)gtlions=# rollback;rollback
-eof-