Hello All,

I am trying to update the value of a field in a table using a sub query and running into issues. My script has a cursor defined with a subquery. I am defining this cursor with a FOR UPDATE clause and when I execute it, I am getting an error as

FOR UPDATE of this query expression is not allowed

I am including the actual script here so that it makes more sense as to what I am doing

declare

/*cursor mycur is select distinct app_id
from table1
where fld1 in (select fld1 from table2 where myfield = 'MYVALUE') for update;*/

cursor mycur is select distinct app_id
from table1 C,table2 D
where C.fld1 = D.fld2
AND myfield = 'MYVALUE' for update;

vc_id table1.app_id%type;
new_id table1.app_id%type;

begin

open mycur;
loop
fetch mycur into vc_id;
new_id := vc_id || '-OLD';
exit when mycur%notfound;
update table1
set app_id = new_id
where current of mycur;

-- dbms_output.put_line('existing ' || vc_id);
-- dbms_output.put_line('new ' || new_id);

end loop;
close mycur;
end;
/

again my question is - Is it possible to use a FOR UPDATE with a subquery or a join?

Appreciate your help