Update with subquery

4 Apr 2014

      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

Update with subquery

George Kollias