SQL - sequential update problem - update using the updating data

Question

Considering this table:

create table x (id int, total int, diff int)

And this data:

[1, 100, 20]
[2, null, 30]
[3, null, -15]
[4, null, 4]
…

I need to calculate the "total" column according to the previous row.

That means that in the end the data should look like that:

[1, 100, 20]
[2, 120, 30]
[3, 150, -15]
[4, 135, 4]
…

What is the most efficient way on doing that?

Answer 1

OK, here's another option. Adding as a separate answer as it's a completely different approach.

The assumption with this is that there are no gaps in the IDs - this may well not be realistic, but it demonstrates the approach. If there are gaps in the IDs, then it should just take a little tweaking on the JOIN.

DECLARE @Data TABLE (ID INTEGER PRIMARY KEY, Total INTEGER, Diff INTEGER)
INSERT @Data VALUES (1, 100, 20)
INSERT @Data VALUES (2, NULL, 30)
INSERT @Data VALUES (3, NULL, -15)
INSERT @Data VALUES (4, NULL, 4)

DECLARE @PreviousTotal INTEGER
SELECT @PreviousTotal = Total
FROM @Data 
WHERE ID = 1

UPDATE d
SET @PreviousTotal = d.Total = @PreviousTotal + d2.Diff
FROM @Data d
    JOIN @Data d2 ON d.ID = d2.Id + 1

SELECT * FROM @Data