sql reset updating rows when a value changes

Question

I have a table as below:

  id    code   value  total
==========================
1     A/01    5       
2     A/01    8       
3     A/01    6       
1     A/02    8       
2     A/02    3       
3     A/02    7       
1     A/03    6       
2     A/03    9       
3     A/03    2

I want to update the total with value of same row + previous row's. I declared a variable as below and update the table:

DECLARE @sum int
SET @sum = 0
UPDATE @table set total = @sum, @sum = @sum + value

It works perfect if I select for the first code:

SELECT * FROM table WHERE code='A/01'

id    code   value  total
==========================
1     A/01    5       5
2     A/01    8       13
3     A/01    6       19

But if I select the whole table, then it does this:

  id    code   value  total
==========================
1     A/01    5       5
2     A/01    8       13
3     A/01    6       19
1     A/02    8       27
2     A/02    3       30
3     A/02    7       37
1     A/03    6       43
2     A/03    9       52
3     A/03    2       54

How can I update the table as I explained so it resets adding values when value in "code" column changes?? Please help, I need the following result, Thanks!

  id    code   value  total
==========================
1     A/01    5       5
2     A/01    8       13
3     A/01    6       19
1     A/02    8       8
2     A/02    3       11
3     A/02    7       18
1     A/03    6       6
2     A/03    9       15
3     A/03    2       17

Why both MySQL and MS SQL Server tags? Which dbms are you using? (Don't tag products not involved...) — jarlh, Nov 17 '15 at 10:21

score 1 · Answer 1 · edited May 23 '17 at 12:22

You can use windowed SUM to calculate total:

WITH cte AS
(
  SELECT a.id ,   a.code , a.[value] , 
  total = SUM(a.[value]) OVER (PARTITION BY code ORDER BY id)
  FROM #mytable a
)
UPDATE m
SET total = c.total
FROM #mytable m
JOIN cte c
  ON m.id = c.id 
 AND m.code = c.code;

SELECT *
FROM #mytable
ORDER BY code, id;

LiveDemo

You cannot use directly windowed function in UPDATE like:

UPDATE #mytable
SET total = SUM([value]) OVER (PARTITION BY code ORDER BY id)

so you need to use subquery/cte to calculate sum first.

EDIT:

Your first attempt:

DECLARE @sum int = 0;
UPDATE @table set total = @sum, @sum = @sum + value;

Can be dangerous and return unpredictable results. (Forget a moment about multiple code and assume there is only 'A/01' in table. You've assumed that sum will work row by row with increasing id.

But there is a catch what if parallelism is involved?

If you need this kind of operation read more about quirky update

Thank you @lad2025, your solution was helpful too! – sqluser Nov 17 '15 at 11:35 — sqluser, Nov 17 '15 at 11:35

score 1 · Accepted Answer · answered Nov 17 '15 at 10:46

1

The Update-Clause affects the whole set. So you can't define that it should reset the @sum after changing the code.

Try using the Window Function Sum(value) Partition by code and Order By ID

For example

SELECT id, value
       Sum(value) OVER (PARTITION BY code ORDER BY id
                            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS total     
FROM [table]

This set you can use to UPDATE your table.

answered Nov 17 '15 at 10:46

CPMunich

725
4
13

Thank you so much @CPMunich for your quick and simple solution. – sqluser Nov 17 '15 at 11:24

sql reset updating rows when a value changes

2 Answers2