CODEWITHSUNDEEP
sqlsql-servert-sql
sqluser
sqluser

Reputation: 403

sql reset updating rows when a value changes

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

Upvotes: 0

Views: 1593

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175786

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

Upvotes: 1

CPMunich
CPMunich

Reputation: 725

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.

Upvotes: 1

Related Questions