Search code examples
oracle-databasecorrelated-subquery

Correlated update gets "single-row subquery returns more than one row"


I am trying to update "my_value" column of table "my_table" based on "a_value" column in "result_table" table, using correlated update. When I run something similar to the following:

my_table:

OrderID  my_volume my_value  
----------------------------
1         100        NULL
2          50        NULL

result_table: 

OrderID  a_value   a_type  
----------------------------
1         2000      BUY
2         3500      SELL

My_table (after running UPDATE query) should become:

OrderID  my_volume my_value  
----------------------------
1         100        2000
2          50        3500

My Update statement:

update my_table set 
my_value=
(select a_value 
from result_table
join my_table on 
result_table.orderID = my_table.orderID )
where orderID in
(select my_table.orderID from my_table,result_table
where
result_table.orderID = my_table.orderID);

I get

"single-row subquery returns more than one row" error.

How the query can be corrected so the SELECT statement return single row for each call?


Solution

  • Best is to use MERGE:

    Merge into my_table mytab
    using result_table  rsltab
     on (mytab.id = rsltab.id)
    
    When matched then
    update
    SET mytab.my_value = rsltab.a_value;