Using two updates where second update statement is using column from first update statement as input

I’ve small doubt about update code block which has been written by someone before and now I’ll be using it in my Java program.

Is it possible to update a column first, then commit and afterwards use same column as an input in another update statement inside same block, as listed in below code. I know using sub-query way to do this but have never seen this way before. It’ll great if someone can confirm

1) Whether it is correct?

2) If not, what can be updated to make it work beyond using sub-query format.

3) Also, bas_capital_calc_cd is column in same table derivatives which is being updated. Can we pass column as an input to functions, such as bas2_rwa_calc here? Moreover, can we pass column name at all in plsql function as input.

Thanks in advance for help!

--BAS_EB_RWA_COMMT  is being used in BAS_EB_TOTAL_CAPITAL calculation. similarly, BAS_AB_RWA_COMMT  is being used in BAS_AB_TOTAL_CAPITAL calculation.

IF ID = 17 THEN     
     UPDATE derivatives 
             SET BAS_CAPITAL_CALC_CD = 'T',  
                 BAS_CATEGORY_CD =  case when nvl(rec.ssfa_resecure_flag,'N') = 'Y' then 911 else 910 end,
                 BAS_EB_RWA_COMMT = bas2_rwa_calc(bas_capital_calc_cd, v_SSFA_COMMT_AMT,v_BAS_CAP_FACTOR_K_COMMT, v_basel_min,v_bas_rwa_rate) + NVL(BAS_CVA_PORTFOLIO_RWA,0),
                 BAS_AB_RWA_COMMT = bas2_rwa_calc(bas_capital_calc_cd, v_SSFA_COMMT_AMT,V_BAS_CAP_FACTOR_K_COMMT, v_basel_min,v_bas_rwa_rate) + NVL(BAS_CVA_PORTFOLIO_RWA,0),
                 BAS_ICAAP_EB_RWA_COMMT = bas2_rwa_calc(bas_capital_calc_cd,bas_unused_commt,bas_icaap_factor_k_commt,v_basel_min,v_bas_rwa_rate),
           WHERE AS_OF_DATE = v_currect_DATE
          COMMIT;

          UPDATE derivatives 
             SET BAS_EB_TOTAL_CAPITAL = round(BAS2_MGRL_CAPITAL(v_date, BAS_EB_RWA, BAS_EB_RWA_COMMT),2),
                 BAS_AB_TOTAL_CAPITAL = round(BAS2_MGRL_CAPITAL(v_date, BAS_AB_RWA, BAS_AB_RWA_COMMT),2)  
           WHERE AS_OF_DATE = v_DATE
             AND ID_NUMBER = rec.ID_NUMBER
             AND IDENTITY_CODE = rec.IDENTITY_CODE;
          COMMIT;
     WHERE AS_OF_DATE = v_currect_DATE;
    COMMIT;
END IF

Answer

In DB2 and the SQL standard you use a feature called FINAL_TABLE to do this. In Oracle you use a statement called “RETURNING”.

cf – https://blog.jooq.org/tag/final-table/

Advertisement

Answer