How to update one table from another table or from aggregation in subqueries?
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
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:
update one table from another table; and
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
Thanks for your reply and clarification!
With Jan2014 release, the two functions can be expressed in the following working forms:
1) 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);
2) update t1 set data = (select sum(t2.data) from t2 where t2.id=t1.idgroup by t2.id) where exists (select * from t2 where t1.id=t2.id);
Cheers,
On Sat, Mar 1, 2014 at 12:50 AM, Niels Nes Niels.Nes@cwi.nl wrote:
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:
update one table from another table; and
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
participants (2)
-
Niels Nes -
Ning Yan