Update with subquery
Hello, Let's assume the script below (version Jan-2014-SP1): 1> select * from checking where id = (select id from account where name='ipzwhsqk'); | id | value | +==+========+ | 1 | 7580.67 | 2> select * from saving where id = (select id from account where name='ipzwhsqk'); | id | value | +==+========+ | 1 | 2599.01 | 3> select case when (select ((select value from saving where saving.id=account.id) + (select value from checking where checking.id=account.id)) from account where name='ipzwhsqk') < 1000000 then checking.value - 1000000 - 1 else checking.value - 1000000 end from checking where checking.id = (select id from account where name='ipzwhsqk'); | ifthenelse_isnull_<_L7 | +==============+ | -992420.33 | -- Update + same select as step 3. It's executed successfully. 4> update checking set value = ( select case when (select ((select value from saving where saving.id=account.id) + (select value from checking where checking.id=account.id)) from account where name='ipzwhsqk') < 1000000 then checking.value - 1000000 - 1 else checking.value - 1000000 end) where checking.id = (select id from account where name='ipzwhsqk'); 5> select * from checking where id = (select id from account where name='ipzwhsqk'); | id | value | +==+========+ | 1 | 7580.67 | So, it seems that the step 4's update has no impact. Am i missing something? Thank you, George
On Fri, Apr 04, 2014 at 04:56:50PM +0300, George Kollias wrote:
Hello,
Let's assume the script below (version Jan-2014-SP1):
1> select * from checking where id = (select id from account where name='ipzwhsqk');
| id | value | +==+========+ | 1 | 7580.67 |
2> select * from saving where id = (select id from account where name='ipzwhsqk');
| id | value | +==+========+ | 1 | 2599.01 |
3> select case when (select ((select value from saving where saving.id=account.id) + (select value from checking where checking.id=account.id)) from account where name='ipzwhsqk') < 1000000 then checking.value - 1000000 - 1 else checking.value - 1000000 end from checking where checking.id = (select id from account where name='ipzwhsqk');
| ifthenelse_isnull_<_L7 | +==============+ | -992420.33 |
-- Update + same select as step 3. It's executed successfully. 4> update checking set value = ( select case when (select ((select value from saving where saving.id=account.id) + (select value from checking where checking.id=account.id)) from account where name='ipzwhsqk') < 1000000 then checking.value - 1000000 - 1 else checking.value - 1000000 end) where checking.id = (select id from account where name='ipzwhsqk');
5> select * from checking where id = (select id from account where name='ipzwhsqk');
| id | value | +==+========+ | 1 | 7580.67 |
So, it seems that the step 4's update has no impact. Am i missing something?
a fix went into the jan2014 release branch today. Niels
Thank you, George _______________________________________________ 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
Thank you!
Cheers,
George
On Wed, Apr 9, 2014 at 2:19 PM, Niels Nes
On Fri, Apr 04, 2014 at 04:56:50PM +0300, George Kollias wrote:
Hello,
Let's assume the script below (version Jan-2014-SP1):
1> select * from checking where id = (select id from account where name='ipzwhsqk');
| id | value | +==+========+ | 1 | 7580.67 |
2> select * from saving where id = (select id from account where name='ipzwhsqk');
| id | value | +==+========+ | 1 | 2599.01 |
3> select case when (select ((select value from saving where saving.id=account.id) + (select value from checking where checking.id=account.id)) from account where name='ipzwhsqk') < 1000000 then checking.value - 1000000 - 1 else checking.value - 1000000 end from checking where checking.id = (select id from account where name='ipzwhsqk');
| ifthenelse_isnull_<_L7 | +==============+ | -992420.33 |
-- Update + same select as step 3. It's executed successfully. 4> update checking set value = ( select case when (select ((select value from saving where saving.id=account.id) + (select value from checking where checking.id=account.id)) from account where name='ipzwhsqk') < 1000000 then checking.value - 1000000 - 1 else checking.value - 1000000 end) where checking.id = (select id from account where name='ipzwhsqk');
5> select * from checking where id = (select id from account where name='ipzwhsqk');
| id | value | +==+========+ | 1 | 7580.67 |
So, it seems that the step 4's update has no impact. Am i missing something?
a fix went into the jan2014 release branch today.
Niels
Thank you, George _______________________________________________ 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)
-
George Kollias -
Niels Nes