Update statement using two or more tables fails

create table t1 (c1 int, c2 int);
create table t2 (c1 int, c2 int);

update t1
set c2 = t2.c2
from t2
where t2.c1 = t1.c1

Result:

SQLException: ERROR: Encountered "from" at line 1, column 26.
Was expecting one of:
<EOF>
"WHERE" ...
"!" ...
";" ...
"," ...
"::" ...
"+" ...
"-" ...
"||" ...
"*" ...
"/" ...
"MOD" ...
"DIV" ...
"%" ...
"&" ...
"|" ...
"#" ...
"<<" ...
">>" ...

Hi Don,

You need to use syntax like this:

update t1
set c2 =
(select t2.c2
from t2
where t2.c1 = t1.c1);

Regards,

Mason Sharp

Wouldn't that be slow when updating multiple columns which is my common update scenario, or are there optimizations to support this?

Hi Don,

I have added multi-column correlated update as a new feature request in our tracking system.

Regards,

Mason

Hi Mason,
Your suggested workaround does work, but fails when a where clause is needed to reduce the rows to be updated. For example:

update t1
set c2 = (select t2.c2 from t2 where t2.c1 = t1.c1)
where exists (select * from t2 where t2.c1 = t1.c1);

Result:

SQLException: ERROR: Failed To Get Results For ( SQL , NodeURL) : ( SELECT ( SELECT "t2"."c2" From "t2" WHERE ("t2"."c1" = "TMPTT433_1"."c1")) as "EXPRESSION1", "c1" as "c1_old", "c2" as "c2_old" FROM "TMPTT433_1" ) eQS Node 13 has aborted execution, cause is: java.sql.SQLException : ERROR: column "c2" does not exist

Hi Don,

That does indeed look like a problem.

We will investigate.

Regards,

Mason