How Can I Fix ORA-01427 in My Oracle UPDATE Statement with a JOIN Subquery?

Update with Join Query in Oracle

An Oracle user encountered an unending execution time for the following query:

UPDATE table1 t1 SET (t1.col,t1.Output) = (
  SELECT t2.col, t3.Output + t2.col
  FROM tabl2 t3 
  LEFT JOIN table1 t2 ON t3.Join_Key = t2.Join_Key
  WHERE t2.col is not NULL);

Unless the SELECT subquery guarantees a single-row result, the UPDATE statement will fail with the error:

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

Correlated updates require a condition linking rows in the outer table (table1 in this case) and the inner subquery. Typically, this condition appears:

AND t1.some_key = t2.some_key);

Additionally, the query updates every row in table1. If the user intends to update specific rows, a WHERE clause that filters based on the join condition should be added, such as:

...
WHERE subquery_condition

The above is the detailed content of How Can I Fix ORA-01427 in My Oracle UPDATE Statement with a JOIN Subquery?. For more information, please follow other related articles on the PHP Chinese website!