Without going through the erroneous ways to try to update joined tables, let me tell you that the only way you can reliably achieve this in Oracle is by using PL/SQL. The reason is that Oracle needs to know beforehand the joined dataset which is to be updated. Thus this needs to be stored in a cursor, and then updated accordingly.
The following piece of PL/SQL code depicts two tables - 'target' and 'source'. We need to update 'target' rows which are found in 'source' by the corresponding value in 'source'.
DECLARE commit_interval number := 1; l_count number := 0; CURSOR c1 is SELECT s.col1 s_col1, s.col2 s_col2, s.col3 s_col3 FROM target t, source s WHERE t.col1 = s.col1 and t.col2 = s.col2 and t.col3 = s.col3; BEGIN FOR r IN c1 LOOP UPDATE target t SET t.col1 = r.s_col1 WHERE t.col2 = r.s_col2 and t.col3 = r.s_col3; l_count := l_count + 1; IF (l_count >= commit_interval) THEN COMMIT; l_count := 0; END IF; END LOOP COMMIT; END;It is important to include all columns in the cursor which are referenced by the WHERE clause in the UPDATE statement.
No comments:
Post a Comment