Updating a column across the table going in loop, how do avoid?

Below update statement is running in a loop, how do i avoid this?

I have 86Million rows in the table

update dbo.pw_part set ptype_pctg=pw.pctg
from (
    select id,ppe,ptype,isnull(SUM(gw) * 100.0 / nullif(SUM(SUM(gw)) OVER (partition by ssn, ppe), 0), 0) as pctg
    from pw_part 
    where year(ppe) <= 2017
    group by id,ppe,ptype,gw
    ) as pw

Table Data as below

ID  date               type         gw              payPercentage
359 2015-03-28          R           1563.79         NULL
359 2015-04-04          M           11.41           NULL
359 2015-04-04          R           1563.79         NULL
359 2015-04-11          M           11.41           NULL
359 2015-04-11          R           1563.79         NULL
359 2015-04-18          M           11.41           NULL
795 2018-01-12          C           2382.39         NULL
795 2018-01-12          M           3.72            NULL
795 2018-01-12          R           3269.23         NULL
795 2018-01-26          C           1437.74         NULL
795 2018-01-26          M           3.72            NULL
795 2018-01-26          R           3269.23         NULL

Desired table should be

ID  date                type        gw          payPercentage
359 2015-03-28          R           1563.79         100
359 2015-04-04          M           11.41          .72            
359 2015-04-04          R           1563.79         99.27         
359 2015-04-11          M           11.41           .72           
359 2015-04-11          R           1563.79         99.27         
359 2015-04-18          M           11.41           100
795 2018-01-12          C           2382.39         42.12         
795 2018-01-12          M           3.72            .06           
795 2018-01-12          R           3269.23         57.80         
795 2018-01-26          C           1437.74         30.52         
795 2018-01-26          M           3.72            .07           
795 2018-01-26          R           3269.23         69.4          

Thank you,

Yum

Answer

Assuming that your query runs, you seem to need a where clause. I would speculate:

update dbo.pw_part
    set ptype_pctg = pw.pctg
from (select id, ppe ,ptype,
             coalesce(SUM(gw) * 100.0 / nullif(SUM(SUM(gw)) OVER (partition by ssn, ppe), 0), 0) as pctg
      from pw_part 
      where year(ppe) <= 2017
      group by id, ppe, ptype
     )  pw
where pw_part.id = pw.id and pw_part.ppe = pw.ppe and
      pw_part.ptype = pw.ptype ;

Note that I removed gw from the group by. It is used as the argument to an aggregation function, so I’m guessing is should not also be a gropu by key.