realtime update needing [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<=t.id)
else (select top(1) price from trans where id=t.relatedid) end)
from trans t

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 Wednesday, April 13, 2011 7:53 AM

[Corporal Agarn]

My output looks like this:

id price relatedid
1 100 NULL
2 100 NULL
3 233 NULL
4 175 NULL
5 500 3

SQL Server 2008 R2

[Wendelius]

For starters, I don't think you have the data in your table what yuo included in the script. In the script you add 5 rows but in the result you have 4 rows, not possible. Seems that you don't have the first row in the database so that makes the different output after the update. If you truncate the test table and try again with 5 rows, are the results expected.

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

[mersad00]

row #5 expected to be updated to 233, not 500

[Corporal Agarn]

Not the way it is written.

select top(1) price from trans where id=t.relatedid

gives the value in the database at the time of the query not after the update. You would need to calculate the maximum average in this query.

[mersad00]

djj55 wrote:

You would need to calculate the maximum average in this query.

how i could do this?