Skip to Main Content
  • Questions
  • Update column in one table with grouped data from another

Breadcrumb

May 4th

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 1000+ times

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:

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 :)