I am attempting using the below statement to update multiple rows:
update mis_user.mis_testr ts
set ts.CURRENT_BALANCE = (select dr.current_balance
from MIS_USER.MIS_DATAREPAIR dr,
where ts.ORGANIZATION = DR.ORGANIZATION
and ts.REPORT_PERIOD = DR.REPORT_PERIOD
and ts.ITEM_NUMBER = DR.ITEM_NUMBER
and ts.STOREROOM = DR.STOREROOM)
However, I keep getting the below error… I’ve tried fixing this with subqueries but to no avail
ORA-01427: single-row subquery returns more than one row
very simple. you have an equal sign fed by a sub-select. that requires the sub-select to retrun just one row. Use IN if you want to handle returning more than one row - but I don;t think that’s what you want really.
also this is a SQL coding question and should have been asked on the SQL forum, not the toad forum since this is not a toad issue.
I will post it there as well, thank you for the quick response. However, I have used IN and an error pops up - ORA-00927: missing equal sign
please first try same query on same database connected with same user in Oracle SQL*Plus. If pass there then might be Toad guilt.
And please if passes in SQL*Plus, compare result run from sqlplus:
SQL> show all
and from Toad (as script or F5 shortcut)
No - it’s purely a SQL problem. The IN won’t work because he is doing s SET, and that requires the sub query to return a single value. So it’s the SQL logic in the sub select that is the issue - not a Toad problem.
@Bert - agreed, do you know a work around? there are 2000 rows of data that i need updated. I know it is doable because one of our DBAs have completed this task for me already. I would be asking him but he’s out of town so i turned to the toad community. I also have the question posted in the sql forum if you would like to answer there.
MIS_USER.mis_testr t is table and nowhere in where (join). So remove it from query and try again
Are you pass the SQL*Plus