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 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!