Update Multiple Rows in a single table

Toad for SQL Server
1

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

Also, when I set the statement to (IN) instead of (=) I get ORA-00927: missing equal sign

any thoughts?

2

Hi jepotter,

if I remember correctly error code like ORA-XXXXX is message generated from Oracle database not Microsoft SQL Server.

Are you sure this is an update statement of SQL Server ?

If it’s an update statement of Oracle DB, you must open discussion on Oracle TOAD forum.

Best regards,

Sergio