Update a column with this query below

Question

I am trying to update a column which is cumulative sum of another..

Here is my query for an acumulative sum. Now I need to update it in my table. Please suggest on this.

SELECT s.[MM], s.[ProjectID], s.[Total_To_Bill],
    ISNULL((
        SELECT SUM(SS.[Total_To_Bill])
        FROM @mtss1 SS WHERE SS.[ProjectID] = s.[ProjectID] 
            AND SS.MM <= S.MM), 0) AS [ProjectedBillable] FROM @mtss1 AS s

If I understand your question, this link might be helpful – Cody Sep 05 '12 at 03:29 — Cody, Sep 05 '12 at 03:29

score 0 · Answer 1 · answered Sep 05 '12 at 15:12

0

Assuming that MM is a unique key on every row, the following accomplished what you want:

with toupdate as (
     SELECT s.[MM],s.[ProjectID], s.[Total_To_Bill],
             Isnull((select SUM(SS.[Total_To_Bill])
                     FROM @mtss1 SS 
                     WHERE  SS.[ProjectID] = s.[ProjectID] and SS.MM <= S.MM) ,0
                    ) As [ProjectedBillable]
     From @mtss1 As s
    )
update t
    set t.cumsum = toupdate.ProjectedBillable
    from toupdate
    where t.mm= toupdate.MM and t.MM

If MM does not define each row, then you need a column or combination of columns that do.

answered Sep 05 '12 at 15:12

Gordon Linoff

1,242,037
58
646
786

I'm worried that since the OP is using a table variable, `UNIQUE` may not be enforceable? – Kermit Sep 05 '12 at 15:56
1

I chose to interpret "Now I need to update it in my table" as meaning that there is a real table separate from the table variable. – Gordon Linoff Sep 05 '12 at 16:01

Update a column with this query below

1 Answers1