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

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

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

Thank u John... I just edited it a couple ago......

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