Update table (col1 through col4) where exists throws single-row returns more than one row error

Question

The sql statement below is throwing an error:

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

UPDATE TABLE_1 T1
   SET (  COL1, COL2, COL3, COL4) = (
       SELECT   col1
             ,  col2
             ,  col3
             ,  col4
         FROM   TEMP_TABLE_2 tt2
        WHERE   tt2.COL_XYZ = t1.COL_XYZ)
 WHERE EXISTS ( select null    
                  FROM TEMP_TABLE_2 tt2
                 WHERE tt2.COL_XYZ = t.COL_XYZ);

I believe the issue is on the "Where exists" because I know the inner select query returns only one row for a given COL_XYZ record.

Any ideas?

SriniV SriniV · Accepted Answer · 2017-12-09T00:24:09

Using Inline View (If it is considered updateable by Oracle)

Note: If you face a non key preserved row error add an index to resolve the same to make it update-able

UPDATE (SELECT t1.col1  AS o_col1, 
               t1.col2  AS o_col2, 
               t1.col3  AS o_col3, 
               t1.col4  AS o_col4, 
               tt2.col1 AS n_col1, 
               tt2.col2 AS n_col2, 
               tt2.col3 AS n_col3, 
               tt2.col4 AS n_col4 
        FROM   table_1 T1 
               inner join temp_table_2 tt2 
                       ON tt2.col_xyz = t1.col_xyz) T 
SET    o_col1 = o_col1, 
       o_col2 = n_col2, 
       o_col3 = n_col3, 
       o_col4 = n_col4;

Update table (col1 through col4) where exists throws single-row returns more than one row error

1 Answers