Any suggestion to fix oracle update statement

user_11114661 0
Viewed 71

I'm trying to update table1.column_name1 with data result from a query

Select ID, column_name1 
from table2  
union  
Select ID, column_name2 
from table3

but I get this error :

ORA-01427: single-row subquery returns more than one row

Any suggestions please, thanks :)

With the following data tables :

table1

ID             column_name1            
----------------------------
26                 null          
30                 null                  
34                 null

table2

ID             column_name1            
---------------------------
26                 fix          
30                 var                  
34                 fix

table3

ID             column_name2            
----------------------------
26                 fix          
30                 null                 
34                 fix

Desired result :

ID             column_name1            
-----------------------------
26                 fix
30                 var
34                 fix

Query:

UPDATE table1 
SET table1.column_name1 = (SELECT b.column_name1 
                           FROM table1 f 
                           JOIN 
                              (SELECT ID, column_name1 
                               FROM table2  
                               UNION
                               SELECT ID, column_name2 
                               FROM table3) b ON f.ID = b.ID);
update-statement inner-join sql-update oracle sql
2 Answers

You can use a query with JOIN rather than UNION such as in order to be able to handle null values which cause too many rows within the UPDATE statement in which a WHERE condition is also needed

UPDATE table1 t1
   SET t1.column_name1 = ( SELECT NVL(t3.column_name2, t2.column_name1)
                             FROM table2 t2
                             JOIN table3 t3
                               ON t3.ID = t2.ID
                            WHERE t3.ID = t1.ID )  
 WHERE EXISTS
           ( SELECT *
               FROM table2 t2
               JOIN table3 t3
                 ON t3.ID = t2.ID
              WHERE t3.ID = t1.ID )

Demo

user_58413060

You are facing issue because you are trying to assign two value (same/different) to single value.

You can use merge as follows:

Merge into table1 t1
Using (SELECT coalesce(t2.column_name1, t3.column_name2) as column_name, t2.id
         FROM table2 t2
         JOIN table3 t3 ON t3.ID = t2.ID) src
ON (src.ID = t1.ID)
When matched then 
Update set t1.column_name1 = src.column_name
user_115656290
Related