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?
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;