Have formatted the above code for our friend and it looks like this
UPDATE ep.f_prc fpa
SET
( given_type,
latest_comment,
latest_comment_datetime,
term_by_year,
coupon_bucket,
pool_of_pools,
state_100pct_name,
reason_code ) = (
SELECT fp.given_type,
CASE WHEN latest_comment_datetime <= TO_DATE('10/10/2022', 'mm/dd/yyyy') THEN
fp.latest_comment
ELSE
( SELECT latest_comment
FROM ep.f_prc
WHERE trunc(snp_date) = TO_DATE('10/07/2022', 'mm/dd/yyyy')
AND instr_id = fpa.instr_id
FETCH NEXT 1 ROWS ONLY
)
END AS "Latest_Comment",
CASE WHEN latest_comment_datetime <= TO_DATE('10/10/2022', 'mm/dd/yyyy') THEN
latest_comment_datetime
ELSE
( SELECT latest_comment_datetime
FROM ep.f_prc
WHERE trunc(snp_date) = TO_DATE('10/07/2022', 'mm/dd/yyyy')
AND instr_id = fpa.instr_id
FETCH NEXT 1 ROWS ONLY )
END "Latest_Comment_Date",
term_by_year,
coupon_bucket,
pool_of_pools,
state_100pct_name,
reason_code
FROM ep.f_prc fp,
op.batchjob op
WHERE fp.snp_date = TO_DATE('10/13/2022', 'mm/dd/yyyy')
AND fp.snp_id = op.snp_id
AND op.batch_name IN ( 'MBS_1500NY_Pricing', 'MBS_1600NY_Pricing' )
AND fp.instr_id = fpa.instr_id
FETCH NEXT 1 ROWS ONLY
)
WHERE
fpa.snp_date BETWEEN TO_DATE('10/10/2022', 'mm/dd/yyyy') AND TO_DATE('10/11/2022', 'mm/dd/yyyy')
AND fpa.snp_id IN ( SELECT snp_id
FROM op.batchjob
WHERE batch_name IN ( 'MBS_1500NY_Pricing', 'MBS_1600NY_Pricing' )
)
AND fpa.instr_id IN ( SELECT instr_id FROM ep.instrument_temp );the driving factor is "fp.instr_id = fpa.instr_id" - which mean for each row in the outer table we run this correlated subquery once to provide the value for set clause.
which i think can be turned into a big bulk operation like this
merge into ep.f_prc fpa using ( select * from ( select fp.instr_id ,
fp.given_type,
case when fp.latest_comment_datetime <= TO_DATE('10/10/2022', 'mm/dd/yyyy') then
fp.latest_comment else t2.latest_comment end as latest_comment ,
case when fp.latest_comment_datetime <= TO_DATE('10/10/2022', 'mm/dd/yyyy') then
fp.latest_comment_datetime else t2.latest_comment_datetime end as latest_comment_datetime,
fp.term_by_year,
fp.coupon_bucket,
fp.pool_of_pools,
fp.state_100pct_name,
fp.reason_code,
row_number() over(partition by fp.instr_id order by fp.latest_comment_datetime ) rn
from ep.f_prc fp,
op.batchjob op,
( select instr_id,
min(latest_comment) keep(dense_rank
first order by latest_comment_datetime desc) as latest_comment ,
max(latest_comment_datetime) as latest_comment_datetime
from ep.f_prc
where snp_date >= TO_DATE('10/07/2022', 'mm/dd/yyyy')
and snp_date < TO_DATE('10/08/2022', 'mm/dd/yyyy')
group by instr_id ) t2
where fp.snp_date = TO_DATE('10/13/2022', 'mm/dd/yyyy')
and fp.snp_id = op.snp_id
and op.batch_name IN ( 'MBS_1500NY_Pricing', 'MBS_1600NY_Pricing' )
and fp.instr_id = t2.instr_id (+) )
where rn = 1 ) t3
on (fpa.instr_id = t3.instr_id )
when matched then
update set
fpa.given_type = t3.given_type ,
fpa.latest_comment = t3.latest_comment ,
fpa.latest_comment_datetime = t3.latest_comment_datetime,
fpa.term_by_year = t3.term_by_year ,
fpa.coupon_bucket = t3.coupon_bucket,
fpa.pool_of_pools = t3.pool_of_pools,
fpa.state_100pct_name = t3.state_100pct_name ,
fpa.reason_code = t3.reason_code ;
so understand the above code and fix if any syntax or typo there - have it tested in your environment and let us know how it goes.