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.