Updating column based on conditions from another table

update

I am updating a column to values based on conditions from another table. I want only the values to be updated for rows where the condition meets. It shouldn't overwrite the rows where the conditions do not meet to null (as other values exists). For e.g.

update i2017_mar_ye a    
set    a.status = (select case 
                            when b.cvg_cstat in ('A','P C','PCC','PEC','PUR','R','RC','RE','PUC','PCR') then 'RE_Others'
                            when b.cvg_cstat in ('B','I','K','N') then 'RE_Lapse'
                            when b.cvg_cstat in ('D') then 'RE_Death'
                            when b.cvg_cstat in ('E','S') then 'RE_Surrender'
                            when b.cvg_cstat in ('H') then 'RE_Rider Claims' 
                          end 
                   from  i2016_mar b
                   where a.pol_id = b.pol_id 
                   and   a.cvg_num = b.cvg_num 
                   and   a.cvg_iss_dt <= to_date('31-March-2016') 
                   and   a.cvg_cstat in ('1','2','3','4') 
                   and   b.cvg_cstat not in ('1','2','3','4') 
                   and   a.cvg_type_cd not in ('DCX','DCW','CWG','CWN','DK2','FK2','DV2','FV2','DX2','FX2','BT1','VT9','VX9','GT3','GX3','CT1','CX1','TX7','OX3','OX4','VT1','VX1','CX2','GX4','TX9'));  
commit;

update i2017_mar_ye a    
set    a.status = (select case 
                            when a.cvg_cstat in ('A','P C','PCC','PEC','PUR','R','RC','RE','PUC','PCR') then 'DE_Others'
                            when a.cvg_cstat in ('B','I','K','N') then 'DE_Lapse'
                            when a.cvg_cstat in ('D') then 'DE_Death'
                            when a.cvg_cstat in ('E','S') then 'DE_Surrender'
                            when a.cvg_cstat in ('F') then 'DE_Maturity'
                            when a.cvg_cstat in ('H') then 'DE_Rider Claims' 
                          end 
                   from  i2016_mar b
                   where rownum = 1 
                   and   a.pol_id = b.pol_id 
                   and   a.cvg_num = b.cvg_num 
                   and   a.cvg_iss_dt <= to_date('31-March-2016') 
                   and   b.cvg_cstat in ('1','2','3','4') 
                   and   a.cvg_cstat not in ('1','2','3','4') 
                   and   a.cvg_type_cd not in ('DCX','DCW','CWG','CWN','DK2','FK2','DV2','FV2','DX2','FX2','BT1','VT9','VX9','GT3','GX3','CT1','CX1','TX7','OX3','OX4','VT1','VX1','CX2','GX4','TX9'));
commit;

update i2017_mar_ye    
set    status = case 
                  when cvg_cstat in ('1','2','4') then 'NB_Inforce'
                  when cvg_cstat in ('A','P C','PCC','PEC','PUR','R','RC','RE','PUC','PCR') then 'NB_Others'
                  when cvg_cstat in ('B','I','K','N') then 'NB_Lapse'
                  when cvg_cstat in ('D') then 'NB_Death'
                  when cvg_cstat in ('E','S') then 'NB_Surrender'
                  when cvg_cstat in ('F') then 'NB_Maturity'
                  when cvg_cstat in ('H') then 'NB_Rider Claims' 
                end   
where  cvg_iss_dt > to_date('31-March-2016') 
and    cvg_cstat not like 'P%' 
and    cvg_cstat not like 'R%'
and    cvg_type_cd not in ('DCX','DCW','CWG','CWN','DK2','FK2','DV2','FV2','DX2','FX2','BT1','VT9','VX9','GT3','GX3','CT1','CX1','TX7','OX3','OX4','VT1','VX1','CX2','GX4','TX9');
commit;

Here only, DE series is updated. RE series gets set to null.

Best Answer

For the first two statements, try:

set    a.status = COALESCE( (select case 
                        ...
                                and   a.cvg_type_cd not in (...)), a.status);

This way, if the select case statement generates a NULL value, you'll set the status back to what it already was.

For the third, try this:

set    status = case 
                  ...
                  when cvg_cstat in ('H') then 'NB_Rider Claims'
                  else status 
                end

Similarly, rather than allowing the case statement to return a NULL value, you force it to return the original status if none of the updated values match.

NOTE: You might get better performance in the first two queries if you modified the query to eliminate rows from a (that is, i2017_mar_ye) that should not be updated. For instance, in the second query, add a WHERE clause on the outer query:

WHERE a.cvg_cstat NOT IN ('A','P C','PCC','PEC','PUR','R','RC','RE','PUC','PCR','B','I','K','N','D','E','S','F','H')
  AND a.cvg_iss_dt <= to_date('31-March-2016') 
  AND a.cvg_cstat not in ('1','2','3','4') 
  AND a.cvg_type_cd not in ('DCX','DCW','CWG','CWN','DK2','FK2','DV2','FV2','DX2','FX2','BT1','VT9','VX9','GT3','GX3','CT1','CX1','TX7','OX3','OX4','VT1','VX1','CX2','GX4','TX9');

You can remove the last three of those lines from the subquery at that point, as well.

Depending on your DBMS, you may be able to use a join between i2017_mar_ye and i2016_mar within the UPDATE statement, but only update i2017_mar_ye. That might allow for better performance as well.

Related Question