asked on

Subquery on the same table

I have a table with four columns

Code
Value
Rank
RollingValue

I want to update the RollingValue to the Value plus the Value in the previous Rank
Initially all the values in the RollingVale are zero

Code Value      Rank       RollingValue
ABCD 7            1            0
ABCD 21       2            0
ABCD 10            3            0

Code Value      Rank       RollingValue
ABCD 7            1            7
ABCD 21       2            28
ABCD 10            3            38

I tried this

UPDATE TableT
SET RollingValue = t.Value + p.Value
FROM TableT t
INNER JOIN
( Select Code, Value, Rank from TableT) AS p
ON t.Code = p.Code
WHERE (t.Rank = (p.Rank + 1))

Louis01

declare @TableT TABLE (Code char(4) not null
                      ,Value int not null
                      ,[Rank] int not null
                      ,RollingValue int not null)

insert into @TableT (Code,Value,[Rank],RollingValue) values ('ABCD', 7, 1, 0)
insert into @TableT (Code,Value,[Rank],RollingValue) values ('ABCD', 21, 2, 0)
insert into @TableT (Code,Value,[Rank],RollingValue) values ('ABCD', 10, 3, 0)

update t1
   set t1.RollingValue = (select SUM(t2.Value) 
                            from @TableT t2
                           where t2.[Rank] <= t1.[Rank])
  from @TableT t1

select * from @TableT

Open in new window

ASKER CERTIFIED SOLUTION

Louis01

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial