MERGE Might Be Helpful Here
Kevin Seymour, July 20, 2017 - 3:20 pm UTC
I like using MERGE for something like this.
MERGE INTO trans a
USING (SELECT tl.transid,
tl.planid,
SUM(tl.totaldolamt * -1) fee
FROM transled tl
WHERE tl.planid = '0003'
AND tl.transtypecd = 'Di'
GROUP BY tl.transid,
tl.planid) t
ON (a.transid = t.transid AND a.planid = t.planid)
WHEN MATCHED THEN
UPDATE
SET a.activityfeeamt = t.fee
WHERE t.fee < a.activityfeeamt;
July 20, 2017 - 4:38 pm UTC
Yep and you have one less access of transled, which should help performance :)