Cursor for UPDATE question

[Edimator]

I need update a column in a table with the sum of all previous rows for each row.

for example:

Day | Amount | SubTotal
1 | 1200 | 1200
2 | 300 | 1500
3 | 2100 | 3600


I search here and look in BOL, and i can't find any syntax or sample about CURSOR for UPDATE.

 

[Rosko]

One of the possibilities is

UPDATE YOURTABLE
SET subtotal =
(SELECT sum(T1.amount)
FROM YOURTABLE T1
WHERE T1.day <= YOURTABLE1.day)

This should do it


GoodLuck
Rosko

 

[bperry]

Purely for discussion purposes (Had nothing better to do this morning!):

I agree with Rosko that his suggestion is the standard approach (although a cursor is sometimes considered in this situation if the table is very large, as those repeated subqueries are going to be painful.)

Anyway, I got to thinking that it's a shame to have to repeat the SUM() over and over again, when actually the previous record only has everything you need to calculate the new subtotal for the current record.
Something like this:

UPDATE YOURTABLE
SET subtotal =
amount +
(SELECT TOP 1 subtotal
FROM YOURTABLE T1
WHERE T1.day <= YOURTABLE.day)

Now, this approach has to access the same number of data pages as Rosko's, so it is no more efficient in terms of IO. (In fact, in generates practically the same execution plan.) But the CPU has to do fewer calculations, since instead of continually summing up all the prior amounts, it just picks up the most recent subtotal, which of course is the sum of all the prior records.

So if the CPU doesn't have to work as hard, then perhaps theoretically this query should run a little quicker. But probably imperceptible on anything but huge datasets (in which case we might be back to looking at a cursor anyway.)