On Fri, Aug 12, 2011 at 02:05:59PM +0200, Stefan Manegold wrote:

On Wed, Aug 10, 2011 at 04:12:24PM +0400, Eugene Prokopiev wrote:

...

Hi,

I have two tables:

table1 ----------- id data ----------- 0 0 1 1 2 2 -----------

table2 ----------- id data ----------- 2 2 3 3 -----------

I need to increment data in big table1 by data from small table2 if it exists or insert absent data from table2 to table1. So result must looks like:

table1 ----------- id data ----------- 0 0 1 1 2 4 3 3 -----------

I used loop by table2 rows and inserts or updates table1 in this loop in PostgreSQL. What is the most effective way to do it in MonetDB?

e.g., using plain declarative SQL:

sql> select * from t1; +------+------+ | id | data | +======+======+ | 0 | 0 | | 1 | 1 | | 2 | 2 | +------+------+ 3 tuples (1.237ms)

sql> select * from t2; +------+------+ | id | data | +======+======+ | 2 | 2 | | 3 | 3 | +------+------+ 2 tuples (1.514ms)

sql> update t1 set data = (select data + t2.data from t2 where t2.id = t1.id) where exists (select t2.id from t2 where t2.id = t1.id); 1 affected row (5.691ms)

sql> select * from t1; +------+------+ | id | data | +======+======+ | 0 | 0 | | 1 | 1 | | 2 | 4 | +------+------+ 3 tuples (1.493ms)

sql> insert into t1 select t2.id, t2.data from t2 left outer join t1 on t2.id = t1.id where t1.id is null;

or: insert into t1 select t2.id, t2.data from t2 where not exists (select t1.id from t1 where t1.id = t2.id);

1 affected row (3.773ms)

sql> select * from t1; +------+------+ | id | data | +======+======+ | 0 | 0 | | 1 | 1 | | 2 | 4 | | 3 | 3 | +------+------+ 4 tuples (1.491ms)

Stefan

-- | Stefan.Manegold @ CWI.nl | DB Architectures (INS1) | | http://CWI.nl/~manegold/ | Science Park 123 (L321) | | Tel.: +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |