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

2005/9/7, Fabian :

Andrei Martsinchyk wrote:

...

Hello Fabian,

I am not sure about 2003, Postgres' docs say that UPDATE ... FROM is their extension.

Ohw, then they have it, like MS SQL Server has it. It is a useful extension.

It could be a useful extension for MonetDB too. It is positioned as a high-performance DBMS for data mining and OLAP applications. Common approach in such applications for uptating huge table is accumulate changes in a small table and apply them all once per n rows or after some time. This is what I am trying to do with MonetDB.

...

Could you suggest any workaround ?

No, it is not possible to do it. You can only do it using a software approach. It's a short comming of the standard IMHO.

Well, at least begin transaction; delete from t1 where id1 in (select id2 from t2); insert into t1 (select * from t2); commit; worked for me. I don't believe they going to include this into standard. This is often implemented as an extension and different between vendors. SQL standards suggest doing this with Cursors. Does MonetDB support updateable cursors ?

...

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.

This is a bug, by the way.

Hmm, it should do what I expected or display different error message ? -- Best regards, Andrei Martsinchyk mailto:andrei.martsinchyk@gmail.com