SQL - sequential update problem - update using the updating data

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 c开发者_JS百科alculate 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?

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 

I'm not too sure about performance of this tbh, so you should test that out on, but this is one way. I'm sure there are other ways, so this is one possibility. As I say, performance would be my main concern.

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 @StartingTotal INTEGER
SELECT @StartingTotal = Total FROM @Data WHERE ID = 1

UPDATE d
SET d.Total = @StartingTotal + TotalDiff
FROM @Data d
    CROSS APPLY (SELECT SUM(Diff) TotalDiff FROM @Data d2 WHERE d2.ID < d.ID) x
WHERE d.Total IS NULL

SELECT * FROM @Data

In the assumption that the sample result is wrong, and it should be like marc_s posted in his comment, you can do it like this:

• For the first record:

  INSERT into X Values(1, 100, 20)
  

• Then for all others (with the corresponding id and diff values in the first and last column):

  INSERT into X SELECT TOP 1 2, total+diff, 30 FROM X ORDER BY id desc
  

Not that nice, but it works.

I would normally not recommend using cursors but in this case it might be a good option. If you worry about performance you need to test the answers given here to figure out what is fastest for you. The best solution might differ depending on the number of rows in the table.

declare @T as table (id int, total int, diff int)

insert into @T values (1, 100, 20)
insert into @T values (2, null, 30)
insert into @T values (3, null, -15)
insert into @T values (4, null, 4)

declare @id int
declare @diff int
declare @total int

select @total = total
from @T
where id = 1

declare cT cursor for select id, diff from @T order by id

open cT
fetch next from cT into @id, @diff
while (@@FETCH_STATUS <> -1)
begin
    update @T
    set total = @total
    where id = @id

    set @total = @total + @diff

    fetch next from cT into @id, @diff
end
close cT
deallocate cT

select *
from @T