Any suggestion to fix oracle update statement

Question

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

Answer 1

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