Skip to Main Content
  • Questions

  • Update column in one table with grouped data from another

Breadcrumb

Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

Question and Answer

Chris Saxon

Thanks for the question, William.

Asked: June 20, 2017 - 12:44 pm UTC

Last updated: July 20, 2017 - 4:38 pm UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

Hello,
I was wondering if it is possible to group related records from one table to use as the data to be updated in another table. It seems logical to me but it is not working. I added a trimmed down version of the two tables with only the required fields used in the update to SQL live. Thank you for any help you can provide

update trans a
set activityfeeamt =
 (
   select sum(tl.totaldolamt * -1)Fee
      from transled tl
   where a.transid = tl.transid and a.planid = tl.planid 
      and tl.planid = '0003' and tl.transtypecd = 'Di'
group by tl.transid, tl.planid, tl.ssnum
      having sum(tl.totaldolamt * -1) < (select t.activityfeeamt from trans t where t.planid = '0003' and tl.transid = t.transid)
);


When I run the script, it updates all records and I want to restrict the update to the records that are less than the activity fee. Instead, the script correctly updates the records less than the activity fee but puts a null or 0 in the other rows.



with LiveSQL Test Case:
https://livesql.oracle.com/apex/livesql/s/e5vw7qmriue42n5o22d0zhghp

and Chris said...

I don't understand exactly what you're trying to do. But if you only want to update a subset of the rows, you need a where clause.

Your check for rows less than the activity fee goes here. Not the having clause of the subquery of the set clause. Something like:

update trans a
set    activityfeeamt = (
      select sum( tl.totaldolamt *-1 ) fee
      from transled tl
      where a.transid        = tl.transid
      and   a.planid         = tl.planid
      and   tl.planid        = '0003'
      and   tl.transtypecd   = 'Di'
      group by tl.transid, tl.planid, tl.ssnum
)
where activityfeeamt > (
      select sum( tl.totaldolamt *-1 ) fee
      from transled tl
      where a.transid        = tl.transid
      and   a.planid         = tl.planid
      and   tl.planid        = '0003'
      and   tl.transtypecd   = 'Di'
      group by tl.transid, tl.planid, tl.ssnum
);

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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;
Chris Saxon
July 20, 2017 - 4:38 pm UTC

Yep and you have one less access of transled, which should help performance :)
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.