Postgresql update a column with cumulative sum

Question

I have a table 'company_abc'. It has 2 primary keys (id, year), the 3rd column is numeric values, the 4rd column should be filled with a cumulative sum of the 3rd column values for each year. I tried the following:

update company_abc 
set srti = ww.srti
    from (SELECT sum(company_abc.salestotal) over (partition by year order by salestotal desc) as stri) ww;

and also tried:

update company_abc 
set (cid, year, srti) = (SELECT company_abc.cid, company_abc.year, sum(company_abc.salestotal) over (partition by year order by salestotal DESC)
    FROM company_abc
    ORDER  BY year desc);

in both cases, it failed.

Answer 1

You seem to want an update with a self-join:

update company_abc c
set stri = c1.stri
from (
    select id, year, 
        sum(salestotal) over(partition by year order by salestotal desc) stri
    from company_abc
) c1
where c1.id = c.year and c1.id = c.id

The subquery computes the window sum (using the same expression as in your original code), and we then use the primary key of the table to update the value on the corresponding row in the table.