Need to update the unpaid_amt in the savings table

Here is my amounts table:

Accountnumber    yearquarter     savingstype    unpaid_amt  statustype
101               20091             mas           -200           w
101               20091             mas            220           p

Here in this savings table whenever there is statustype type called 'w' within that Accountnumber,yearquarter,savingstype the status other than 'w' should be updated with the sum within that Accountnumber,yearquarter,savingstype we need to update the each and every unpaid_amt in savingstype for 'w' statustype's as "ZERO"

consider the account number 101 : In that savingstype mas it has a status called 'w' and sum of unpaid _amt is "twenty" so we need to make un paid amt ashown below:

Accountnumber    yearquarter     savingstype    unpaid_amt  statustype
101               20091             mas              0           w
101               20091             mas             20           p

1 answer

  • answered 2018-03-13 21:22 Vamsi Prabhala

    You can use a cte (to calculate the total unpaid_amt for the group) to update.

    with to_update as (select t.*
                       ,sum(unpaid_amt) over(partition by Accountnumber,yearquarter,savingstype) as total_unpaid
                       from tbl t
                      ) 
    update to_update
    set unpaid_amt=case when statustype='w' then 0 else total_unpaid end