(aix 5.1, db2 8.1.6)
Consider this toy example:
--------------------
create table foo ( alpha int, beta int );
insert into foo ( 100, -1 );
insert into foo ( 200, -1 );
insert into foo ( 300, -1 );
update foo x set beta = ( select count(*) from foo y where y.beta >= 0 );
--------------------
I would swear I ran code like this ages ago,
and it generated smth like these results:
: alpha beta :
: 100 0 :
: 200 1 :
: 300 2 :
I expected something like this because the update
will evaluate each row in foo.
The first time it evaluates the nested-select,
all values of beta are negative, so the count(*) will
yield 0.
The 2nd time it evaluates the nested-select,
most values of beta are negative (one is zero),
so the count(*) should yield 1.
etc.
What I'm getting is:
: alpha beta :
: 100 0 :
: 200 0 :
: 300 0 :
Now I really do not care about the order of alpha: any permutation
would be fine, e.g.
: alpha beta : alpha beta : alpha beta :
: 100 2 : 100 1 : 100 2 :
: 200 1 : 200 0 : 200 0 :
: 300 0 : 300 2 : 300 1 :
Is there a query option or smth I can set to make db2/udb's
update work like this?
Thanks, John G.