This content has been marked as final. Show 6 replies
And see how namy rows it returns. Based on "I dont find any updates" most likely your query returns no rows. If so, examine join condition logic.
select count(*) FROM A,B,C WHERE ... complicated join on the 3 tables /
Yeah that's what I thought initially, so I ran that select query by itself and found 106 records. But then when I checked the see if the actual field is updated, I only see NULLs. I double checked to make sure I am updating the correct column with the correct values - none of the values I pull are NULL.
Then I tried this:
for m in ( <Same exact select as the one above, minus the "for update"> ) loop
update A set A.ID = B.ID where <repeat of where caluse in the join>
And this works fine. So I cant understand why it didnt work if I tried it with using a cursor
trant wrote:Oops, missed current of. You cannot use the CURRENT OF clause with a cursor declared with a join* since internally, the CURRENT OF mechanism uses the ROWID pseudocolumn and there is no way to specify which table the ROWID relates to:
And this works fine.
So something doesn't add up. In any case, you need to add A.ROWID to cursor and use it in update:
SQL> DECLARE 2 v_sal NUMBER(8,2); 3 CURSOR c1 IS SELECT sal FROM emp e,dept d where e.deptno = d.deptno FOR UPDATE; 4 BEGIN 5 OPEN c1; 6 LOOP 7 FETCH c1 INTO v_sal; 8 UPDATE emp SET sal = sal * 1.02 WHERE CURRENT OF c1; 9 EXIT WHEN c1%NOTFOUND; 10 END LOOP; 11 END; 12 / DECLARE * ERROR at line 1: ORA-01410: invalid ROWID ORA-06512: at line 8 SQL>
SQL> DECLARE 2 v_sal NUMBER(8,2); 3 v_rid rowid; 4 CURSOR c1 IS SELECT sal,e.rowid FROM emp e,dept d where e.deptno = d.deptno FOR UPDATE; 5 BEGIN 6 OPEN c1; 7 LOOP 8 FETCH c1 INTO v_sal,v_rid; 9 UPDATE emp SET sal = sal * 1.02 WHERE rowid = v_rid; 10 EXIT WHEN c1%NOTFOUND; 11 END LOOP; 12 END; 13 / PL/SQL procedure successfully completed. SQL>
Ah great, now I understand - thank you very much for that !
Edit: can you tell me, which of these approaches would be more efficient? using the cursor to loop and update on rowid or doing that in line select in for loop followed by update on repeated where clause? I tend to think its the former but just wanted to make sure
Edited by: trant on Jun 26, 2011 8:49 AM
Most efficient would be plain update:
update A A1 set A.ID = ( select B.ID FROM A,B,C WHERE ... complicated join on the 3 tables AND A.ROWID = A1.ROWID ) where rowid in ( select a.rowid FROM A,B,C WHERE ... complicated join on the 3 tables ) /
As Solomon says, I would always try a plain UPDATE first.
Occasionally however, it can be more efficient to use updateable view syntax:
This requires foreign key constraints to be in place so that the result set is deterministic. If they are not, you might try the equivalent MERGE statement.
update ( select a.x as oldvalue, c.y as newvalue from a,b,c where ... complicated join on the 3 tables ) set oldvalue = newvalue
For small numbers of rows (i.e. where the cost is in retrieving the rows and the number of actual updates is trivial), a PL/SQL loop could be OK as well.