Updating Multiple Rows

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,
MIS_USER.mis_testr t
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

any thoughts?

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

@jepotter,

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)

show all

Brg

Damir

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

Thanks.

Are you pass the SQL*Plus