correlated update headache (db2 unix)

[jgreve]

(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.

[jgreve]

Sorry, never mind. My memory was probably playing tricks on me. If I knew how to delete a thread, I'd kill this one.

Trying to read from a modified table in-flight is probably goes against some principle in sql's data model.

(Though if you do know of a way to do this, please tell me :-)