On Fri, Feb 28, 2014 at 04:04:53PM -0800, Ning Yan wrote:
> Hi,
>
>
> Firstly, I'm using MonetDB branch from MonetDB Database Server Toolkit
> v1.1 (Feb2013-SP1)
>
>
> I have two tables, and want to complete the following two functions
> without creating a temporary table:
>
>
> 1) update one table from another table; and
>
> 2) update one table from aggregation of another table.
>
>
> I figured out how to do them in MySQL and Postgresql but similar
> queries failed in MonetDB. 
>
>
>
>
> For 1), Suppose I have the following two tables:
>
>
> drop table t1; create table t1(id int, data int);
>
> drop table t2; create table t2(id int, data int);
>
> delete from t1; insert into t1 values (0, 0), (1,1), (2,2); select*from
> t1;
>
> delete from t2; insert into t2 values (1,2), (2, 3), (3,4); select*from
> t2;
>
>
> We need to set t1.data to t1.data+t2.data when their ids match, t1.id=
> t2.id.
>
>
> In MySQL, we can use:
>
>
> update t1 inner join t2 on t1.id=t2.id set t1.data=t1.data+t2.data;
>
>
> In Postgresql, we can use:
>
>
> with tmp as (select t1.id, t1.data+t2.data as data from t1,t2 where
> t1.id=t2.id) update t1 set data = tmp.data from tmp where t1.id=tmp.id;
> select*from t1;
>
>
> Or for both, we can use:
>
>
> update t1 set data=(select t1.data+t2.data from t2 where t2.id=t1.id)
> where exists (select * from t2 where t1.id=t2.id);

With the current Jan2014 version this works.

Niels

>
>
> But none of them work in MonetDB.
>
>
>
> This question is similar to question asked before (https://
> www.monetdb.org/pipermail/users-list/2011-August/005072.html), but the
> answered suggested sees to be incorrect. There the two tables having
> the same value for tuple id=2.
>
>
> update t1 set data=(select data+t2.data from t2 where t2.id=t1.id)
> where exists (select * from t2 where t1.id=t2.id);
>
>
> Thus in this query, the data field in 1st subquery of select actually
> comes from t2 not t1. However, if we changed to t1.data, the parser
> will not recognize name t1. It is somehow wired that it can recognize
> t1.id in condition clause.
>
>
> Not that the following query is also not correct:
>
> update t1 set data=(select t1.data + t2.data from t1, t2 where t2.id =
> t1.id) where exists (select t2.id from t2 where t2.id = t1.id);
>
>
> As the subquery becomes independent from external query, and might
> result in multiple values which violates cardinality of assignment.
>
>
> Also changing field names in t1 and t2 will not be helpful either.
>
>
>
> For 2), Suppose we have the following two tables:
>
>
> delete from t1; insert into t1 values (0, 0), (1,1), (2,2); select *
> from t1;
>
> delete from t2; insert into t2 values (1,2), (2, 3), (3,4), (2,5);
> select * from t2;
>
>
> We want to aggregate values in t2 and assign results to t1.
>
>
> In MySQL, we can use:
>
>
> update t1 inner join t2 on t1.id=t2.id set t1.data=(select sum(t2.data)
> from t2 where t2.id=t1.id group by t2.id);
>
>
> Or in Postgresql, we can use:
>
>
> with tmp as (select t2.id, sum(t2.data) as data from t2 group by t2.id)
> update t1 set data=tmp.data from tmp where t1.id=tmp.id;
>
>
> In MonetDB, how to correctly write such subquery in update statement?

update t1 set data=(select sum(t2.data) from t2 where t1.id = t2.id
group by t2.id) where exists (select * from t2 where t1.id=t2.id);

>
>
> It looks like that MonetDB supports with clause in select, but not in
> update.

We will fix the with in the next feature release.

>
>
> with tmp as (select t2.id, sum(t2.data) as data from t2 group by t2.id)
> select * from tmp;
>
>
>
> I'm also interested to know the performance difference between using
> subqueries and creating temporary tables.

in general subqueries should be better, because subqueries can be rewriten by
the optimizer.
>
Niels

>
>
> Thanks!
>
>
> Regards,
>
>
>
> -- 
> Ning Yan
>
>

> _______________________________________________
> users-list mailing list
> users-list@monetdb.org
> https://www.monetdb.org/mailman/listinfo/users-list


--
Niels Nes, Centrum Wiskunde & Informatica (CWI)
Science Park 123, 1098 XG Amsterdam, The Netherlands
room L3.14,  phone ++31 20 592-4098     sip:4098@sip.cwi.nl
url: http://www.cwi.nl/~niels   e-mail: Niels.Nes@cwi.nl

_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list