Update SQL Table Column in same table with Running Total

August 4, 2022

I have three fields in my table called SCURVE fields are id, DailyPlanned and CumPlanned
I have inserted around 300 values into the DailyPlanned field, I am trying to Update the CumPlanned with a running total as the ID increases. I have tried several methods but this does give me the running total

SELECT SUM (DailyPlanned) OVER (ORDER BY Id) AS RunningTotal FROM SCURVE

I tried using the following to update the value of CumPlanned that has the same ID with calculatyed running total, something like Update

UPDATE SCURVE SET CumPlanned = (Select SUM (DailyPlanned) OVER (ORDER BY Id) AS RunningTotal 
    From SCURVE S 
    Where S.id=SCURVE.id)

The other suggestion on stack was here
LINK

No luck with it either

WITH SCURVE AS
 (SELECT id, DailyPlanned,
         Sum(DailyPlanned) OVER (partition BY id) AS RunningTotal 
  FROM SCURVE
 ) UPDATE SCURVE SET CumPlanned = RunningTotal

My Table:

CREATE TABLE [dbo].[SCURVE](
[id] [int] IDENTITY(1,1) NOT NULL,
[CumPlanned] [decimal](3, 0) NULL,
[DailyPlanned] [decimal](3, 0) NULL) 
ON [PRIMARY]

>Solution :

I suggest not proceeding with this update operation, as the rolling sum is derived data and probably should always be computed on the fly. That being said, if you really must do this, an updatable CTE should work:

WITH cte AS (
    SELECT CumPlanned, SUM(DailyPlanned) OVER (ORDER BY Id) AS RunningTotal
    FROM SCURVE
)

UPDATE cte
SET CumPlanned = RunningTotal;