How to update one table from another table or from aggregation in subqueries?

1 Mar 2014

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

 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?

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

 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.

 Thanks!

 Regards,

 --
Ning Yan