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