updating multiple rows

[crsdev]

Hello All,
I am trying to update multiple rows in a table with the following query:

update some_table
set (discount_rates)=
(select value
from some_table_a aa, some_table bb
where aa.key=bb.id);

I am getting the following error.
SQL Error: ORA-01427: single-row subquery returns more than one row

Thanks in advance.

 

[SantaMufasa]

CRSDev,

I believe your problem results from your not properly correlating your outer UPDATE table with your inner table. If some_table_a.key is unique, then to get rid of the error you are experiencing (and to improve the overall performance), you can alter your code to read:

update some_table bb
set (discount_rates)=
     (select max(value)
     from some_table_a aa
     where aa.key=bb.id)
where exists (select 'x' from some_table_a
               where where aa.key=bb.id);

Try this alternative and let us know the outcome.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]