Join Update vs Sub-selects update

Development said the use of multi-table Association to update the time found that there is no correct update record (after the discovery is a conditional problem), has not been how to use the association update, this time to see, found a problem, when the return of multiple rows of records will not be like the traditional subquery update as the error, but randomly select a record to update ( looks like the last one? )
Therefore, although the association update will be much faster, but to consider this uncertainty caused by the hidden dangers.

gtlions=# CREATE TABLE joinupdate1 (ID int,name varchar (20));  Notice:table doesn ' t has ' 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 is the optimal data distribution key to minimize skew. Create tablegtlions=# CREATE TABLE Joinupdate2 (ID int,name varchar (20));  Notice:table doesn ' t has ' 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 is 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 ');  SERT 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 ') and 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-

Join Update vs Sub-selects update