How to Fix an Oracle UPDATE Query with JOIN That Runs Indefinitely?

Oracle Update Query with Join

Consider the following Oracle update 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);

Issue:

The query executes indefinitely due to a missing condition that would ensure the subquery returns a single row for each updated row in t1.

Solution:

To resolve the issue, a condition must be added to the subquery to correlate the rows in t1 with those in the subquery. For example, the following condition could be used:

AND t1.some_key = t2.some_key

The resulting updated query would look like this:

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
    AND t1.some_key = t2.some_key);

Additional Considerations:

• The above modification ensures that the subquery returns a single row for each t1 row that meets the added condition.
• If it is not intended to update every row in t1, an additional WHERE clause could be added to the main UPDATE statement to specify the rows to be updated.

The above is the detailed content of How to Fix an Oracle UPDATE Query with JOIN That Runs Indefinitely?. For more information, please follow other related articles on the PHP Chinese website!