April 13, 2011 at 5:02 am
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
April 13, 2011 at 5:14 am
Please will you post your actual update statement, since the one you posted has two syntax errors in it.
Thanks
John
April 13, 2011 at 5:21 am
I tried to execute your update statement in SQL 2005 and '=<' is not working on that hence I modified to '<=' as follows.
Update TempTrans
set price=(case when relatedid is null then (select avg(price) from TempTrans where id<=t.id)
else (select top(1) price from TempTrans where id=t.relatedid) end)
from TempTrans t
and the following is the output after executing update statement.
idpricerelatedid
------------------------------------
1100NULL
2100NULL
3233NULL
4175NULL
55003
- Manish
April 13, 2011 at 5:51 am
Manish_ (4/13/2011)
I tried to execute your update statement in SQL 2005 and '=<' is not working on that hence I modified to '<=' as follows.Update TempTrans
set price=(case when relatedid is null then (select avg(price) from TempTrans where id<=t.id)
else (select top(1) price from TempTrans where id=t.relatedid) end)
from TempTrans t
and the following is the output after executing update statement.
idpricerelatedid
------------------------------------
1100NULL
2100NULL
3233NULL
4175NULL
55003
after update value of row #3 is 233 ... look at the result row #5 has updated to 500 which it's before update value of row #3 , this is my exact problem! row #5 must be updated to 233
April 13, 2011 at 5:55 am
John Mitchell-245523 (4/13/2011)
Please will you post your actual update statement, since the one you posted has two syntax errors in it.Thanks
John
Thank u John... I just edited it a couple ago... plz take look at it
April 13, 2011 at 8:03 am
It looks like you will need to perform 2 updates, one for the average and a second for the related price update.
UPDATE trans
SET price = (select avg(price) from trans where id<=t.id)
from trans t
WHERE relatedid IS NULL
UPDATE trans
SET price = (select top(1) price from trans where id=t.relatedid)
From trans t
WHERE relatedid > 0
April 13, 2011 at 8:08 am
You need to do your update in two passes:
;WITH t1 AS (SELECT id,price,relatedid FROM trans)
UPDATE trans
SET price=(
SELECT AVG(price)
FROM t1
WHERE t1.id<=trans.id
)
WHERE relatedid IS NULL
;WITH t1 AS (SELECT id,price,relatedid FROM trans)
UPDATE trans
SET price=(
SELECT TOP(1) price
FROM t1
WHERE t1.id=trans.relatedid
)
WHERE relatedid IS NOT NULL
John
April 13, 2011 at 8:45 am
Another way with just one update.
UPDATE trans
SET price = CASEWHEN relatedID IS NOT NULL
THEN
(select avg(price) from trans where id<=t.relatedid)
ELSE
(select avg(price) from trans where id<=t.id)
END
FROM trans t
April 13, 2011 at 12:30 pm
John Mitchell-245523 (4/13/2011)
You need to do your update in two passes:
;WITH t1 AS (SELECT id,price,relatedid FROM trans)UPDATE trans
SET price=(
SELECT AVG(price)
FROM t1
WHERE t1.id<=trans.id
)
WHERE relatedid IS NULL
;WITH t1 AS (SELECT id,price,relatedid FROM trans)
UPDATE trans
SET price=(
SELECT TOP(1) price
FROM t1
WHERE t1.id=trans.relatedid
)
WHERE relatedid IS NOT NULL
John
In two step update method the result goes wrong! because the value of not null relatedid rows are clearly involves in avg operation! I mean when row #3 has wrong value in step one avg of next row is wrong also, so this operation must executed in one step!
April 13, 2011 at 12:35 pm
DB Dan (4/13/2011)
Another way with just one update.
UPDATE trans
SET price = CASEWHEN relatedID IS NOT NULL
THEN
(select avg(price) from trans where id<=t.relatedid)
ELSE
(select avg(price) from trans where id<=t.id)
END
FROM trans t
Thank you DB Dan! I LOVE THIS I must implement this in real project! I'll be right back
April 14, 2011 at 1:46 am
DB Dan (4/13/2011)
Another way with just one update.
UPDATE trans
SET price = CASEWHEN relatedID IS NOT NULL
THEN
(select avg(price) from trans where id<=t.relatedid)
ELSE
(select avg(price) from trans where id<=t.id)
END
FROM trans t
result goes wrong again! the problem is here... next avg row needs before updated row value! the problem in before related row has been solved but for next avg rows problem still exists! PLZZZ help :crying:
April 21, 2011 at 1:01 pm
simorgh,
A set based update works on the SELECT statement, which is processed and returns a record set before any updates are actually done. If you expect a cumulative sequential update on a column that is has the update information from a column that is earlier in the sort order you can't do it in a single update statement. There are 2 options for this.
A cursor that updates single rows in sequence or build a temp table with the expected accumulated results (see Jeff Moden's article on the quirky update method for running totals) and use that to join to your table and do the update.
Todd Fifield
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply