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