joinupdatevssub-selectsupdate

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 TABLE
gtlions=# 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 TABLE
gtlions=# insert into joinupdate1 values(1,'a');
INSERT 0 1
gtlions=# insert into joinupdate1 values(2,'b');
INSERT 0 1
gtlions=# insert into joinupdate2 values(1,'b');
INSERT 0 1
gtlions=# insert into joinupdate2 values(2,'b');
INSERT 0 1
gtlions=# truncate table joinupdate2;
TRUNCATE TABLE
gtlions=# insert into joinupdate2 values(1,'c');
INSERT 0 1
gtlions=# insert into joinupdate2 values(2,'d'); 
INSERT 0 1
gtlions=# 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;
BEGIN
gtlions=# update joinupdate1 set name=(select name from joinupdate2 where joinupdate1.id=joinupdate2.id);
UPDATE 2
gtlions=# select * from joinupdate1;
 id | name 
----+------
 1 | c
 2 | d
(2 rows)

gtlions=# rollback;
ROLLBACK
gtlions=# end;
WARNING: there is no transaction in progress
COMMIT
gtlions=# select * from joinupdate1;
 id | name 
----+------
 1 | a
 2 | b
(2 rows)

gtlions=# begin;
BEGIN
gtlions=# update joinupdate1 set name=joinupdate2.name from joinupdate2 where joinupdate1.id=joinupdate2.id;
UPDATE 2
gtlions=# select * from joinupdate1;
 id | name 
----+------
 1 | c
 2 | d
(2 rows)

gtlions=# rollback;
ROLLBACK
gtlions=# insert into joinupdate2 values(1,'e');
INSERT 0 1
gtlions=# begin;
BEGIN
gtlions=# 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;
ROLLBACK
gtlions=# begin;
BEGIN
gtlions=# update joinupdate1 set name=joinupdate2.name from joinupdate2 where joinupdate1.id=joinupdate2.id;
UPDATE 2
gtlions=# 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 2
gtlions=# 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 2
gtlions=# select * from joinupdate1;
 id | name 
----+------
 1 | e
 2 | d
(2 rows)

gtlions=# insert into joinupdate2 values(1,'f');
INSERT 0 1
gtlions=# update joinupdate1 set name=joinupdate2.name from joinupdate2 where joinupdate1.id=joinupdate2.id;
UPDATE 2
gtlions=# select * from joinupdate1;
 id | name 
----+------
 1 | f
 2 | d
(2 rows)

gtlions=# rollback;
ROLLBACK