update child_cond set s_trd= (select s_trd (select max(tv.s_trd) s_trd, tv.tradeid tradeid, tv.trd_ver trd_ver, tv.timest timest, tv.sourcename sourcename, stg_trade.sp_cond sp_cond master_t tv, t_stg_trade_version stg_trade tv.sourcename = stg_trade.sourcename , tv.tradeid = stg_trade.tradeid , tv.trd_ver = stg_trade.trd_ver , tv.timest = stg_trade.timest , tradesetid = '91004' group tv.tradeid, tv.trd_ver, tv.timest, tv.sourcename, stg_trade.sp_cond )trd child_cond.sp_cond = trd.sp_cond , s_trd not null ) i need update foreign key in child table based on primary key master table. when there no value in child table, child foreign key value getting updated null. s_trd being set null when new master records inserted in master_t there no corresponding child records in child_cond
this merge statement cater ,only matched , not null s_trd updated child_cond table
merge child_cond cc using ( select max (tv.s_trd) s_trd, tv.tradeid tradeid, tv.trd_ver trd_ver, tv.timest timest, tv.sourcename sourcename, stg_trade.sp_cond sp_cond master_t tv, t_stg_trade_version stg_trade tv.sourcename = stg_trade.sourcename , tv.tradeid = stg_trade.tradeid , tv.trd_ver = stg_trade.trd_ver , tv.timest = stg_trade.timest , tradesetid = '91004' group tv.tradeid, tv.trd_ver, tv.timest, tv.sourcename, stg_trade.sp_cond) trd on (cc.sp_cond = trd.s_trd , trd.s_trd not null ) --remove filter ,incase need copy null s_trd value child_cond when matched when matched update set cc.s_trd = trd.s_trd 
Comments
Post a Comment