MonetDB

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.html

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 ;)

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@...
> https://lists.sourceforge.net/lists/listinfo/monetdb-users

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.html
> 
> 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@...
> > 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@...
> 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@...