On Fri, Jul 04, 2008 at 06:11:29PM +0200, Romulo Goncalves wrote:

Kirk Abbott wrote:

...

Hello,

I am having trouble with the syntax for doing an update using 2 tables. In postgres, one can do something like:

update table1 set name = table2.name from table2 where table1.id = table2.id ;

In oracle (it's something like)

update table1 a set name = (select b.name from table2 b where a.id = b.id) ;

What is the correct way to do this in MonetDb (as both of the above fail) You can see the syntax here: http://monetdb.cwi.nl/projects/monetdb/SQL/Documentation/Data-Manipulation.h...

You did a little mistake on your update statement, in the from clause of your select you forgot table1 ;)

update table1 set name = (select b.name from table2 b, table1 a where a.id = b.id) ;

That should work ;) No he did name table1 as a. The missing part is the 'where' for the update, ie specify which values from a need updating.

Example from the test set create table t2 (id2 int, val2 varchar(255)); create table t1 (id1 int, val1 varchar(255)); insert into t1 values (1,'1'); insert into t2 values (1,'2'); update t1 set val1 = (select val2 from t2 where id1 = id2) where id1 in (select id2 from t2); select * from t1; select * from t2; drop table t1; drop table t2; Niels

Regards, Romulo

...

Thanks, Kirk.

------------------------------------------------------------------------- Sponsored by: SourceForge.net Community Choice Awards: VOTE NOW! Studies have shown that voting for your favorite open source project, along with a healthy diet, reduces your potential for chronic lameness and boredom. Vote Now at http://www.sourceforge.net/community/cca08 _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------- Sponsored by: SourceForge.net Community Choice Awards: VOTE NOW! Studies have shown that voting for your favorite open source project, along with a healthy diet, reduces your potential for chronic lameness and boredom. Vote Now at http://www.sourceforge.net/community/cca08 _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

-- Niels Nes, Centre for Mathematics and Computer Science (CWI) Kruislaan 413, 1098 SJ Amsterdam, The Netherlands room C0.02, phone ++31 20 592-4098, fax ++31 20 592-4312 url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl