Hello Fabian,
I am not sure about 2003, Postgres' docs say that UPDATE ... FROM is
their extension.
Could you suggest any workaround ?
I tried correlated subselect, it does not work:
monetdb-> create table t1 (id1 int, val1 varchar(255));
Operation successful
monetdb-> create table t2 (id2 int, val2 varchar(255));
Operation successful
monetdb-> insert into t1 values (1,'1');
1 affected row
monetdb-> insert into t2 values (1,'2');
1 affected row
monetdb-> update t1 set val1 = (select val2 from t2 where id1 = id2)
where id1 in (select id2 from t2);
Error: ERROR: interpret: unknown variable 's23'.
ERROR: interpret_params: join(param 2): evaluation error.
2005/9/7, Fabian
Hi Andrei,
If I recall correctly, this use of the UPDATE is not in the SQL99 standard. I would have to check against SQL2003 to see if it now it, because PostgreSQL is in general quite compliant with the spects.
The short answer for now is "no". It is not accepted by the parser at the moment, and surely is not supported in the frontend.
Do you know perhaps if this is in the 2003 standard? Someone else who knows/can check?
Andrei Martsinchyk wrote:
Hello All,
Does SQL frontent support updating one table with data from another table ? More formally, if I have two tables
create table t1 (id1 int, val1 varchar(255)); and create table t2 (id2 int, val2 varchar(255)); ,
can I execute statement like this (PostgreSQL syntax):
update t1 set val1=val2 from t2 where id1=id2; ?
Thanks in advance.
-- Best regards, Andrei Martsinchyk mailto:andrei.martsinchyk@gmail.com