refresh values after each row update [modified]

[mersad00]

hi everybody, I have a big problem with update statement, let me mention the situation by exact script and results! first create trans table=>

create table trans(
id bigint identity(1,1) not null,
price decimal(18,0) null,
relatedid bigint null)

Then fill it by this script

Insert into trans (price,relatedid)values(100,NULL)
Insert into trans (price,relatedid)values(100,NULL)
Insert into trans (price,relatedid)values(500,NULL)
Insert into trans (price,relatedid)values(1,NULL)
Insert into trans (price,relatedid)values(1,3)

Now run this query!

Update trans
set price=(case when relatedid is null then (select avg(price) from trans where id=
After execution of query I expect this result:
id-----price-----relatedid
1 100 NULL
2 300 NULL
3 200 NULL
4 200 3 <======== realy expected!!!!

But this is the result!!!
id-----price-----relatedid
1 100 NULL
2 300 NULL
3 200 NULL <======= #3 row
4 1 3 <===== wrong value, i mean this is the value before update

row with id 3 has been updated before but update doesnt know this!!!!!

WHAT I NEED i need to get expected result from this execution!!!! plzzzzzzzzzzz

modified on Tuesday, April 12, 2011 10:34 AM

[Mycroft Holmes]

It sounds like you have processing half in your database and half in you application. Change the design to move all the processing into one platform or make sure the database returns the results from the update (possibly with a simple select from updated record changing the return type to a datatable)

Never underestimate the power of human stupidity RAH

[Wendelius]

So are you saying that the function doesn't see the refreshed situation. If that's the case, perhaps you're running the update in a different transaction or you are feeding 'old' values as parameters to the function. The function should be able to fetch the current data from the database even if the transaction is still going on as long as you use the same transaction.

The need to optimize rises from a bad design.My articles[^]

[mersad00]

plz take look at edited message...

[Wendelius]

Answerred the later post.

The need to optimize rises from a bad design.My articles[^]