I am trying to update one table using a subquery that totals several transactions from another table. I cannot figure how to link specific rows from my parent table to the rows in my subquery. I keep getting the ORA-01427: Subquery returns more than one row. I was hoping to update several records at a time by joining the update table to the subquery. When I break down each section into select statements, I get the expected results. Below is the script I am using.
update trans a
set activityfeeamt =
(select sum(transled.totaldolamt * -1)Fee
from transled,
(select planid, transid, activityfeeamt from trans where planid = '0003') t
where transled.planid = t.planid and transled.transid = t.transid
and a.transid = t.transid and a.transid = transled.transid
and transled.transtypecd = 'Di'
and transled.planid = '0003'
group by transled.transid, transled.planid, transled.ssnum, t.activityfeeamt
having sum(transled.totaldolamt * -1) < t.activityfeeamt)
where a.planid = '0003'
and a.transid in
(select transid
from trans
where planid = '0003' and transid in
(select transid from transled where planid = '0003'
and transtypecd = 'Di' and transid in (176, 179))
The query that's providing the values for your column must return at most one row for each row in trans.
So you need to find a way to associate each row in the subquery with one row in the table you're updating:
update t1
set col = (
select ...
from t2
where t1.xyz = t2.abc -- this join must give one row for each row of t1
);
To explain how to fix your query we need to know more about your data. So if you need further help with this we'll need a test case that includes:
- Create table statements for your tables
- Sample data (in the form of inserts) that show the error.