I am trying to run a simple program for updating the value of a field using a cursor and get an error message as FOR UPDATE of this query expression is not allowed. My question - is a FOR UPDATE allowed with a subquery or a join? I am including the code below so that I make more sense

I tried a sub-query and a join and both give the same error message.

declare

/*cursor mycur is select distinct vcomm_id
from mytable1
where fld3 in (select fld3 from mytable2 where fld2 = 'MYVAL') for update;*/

cursor mycur is select distinct app_id
from mytable1 C,mytable2 D
where C.fld1 = D.fld1
AND d.fld2 = 'MYVAL' for update;

vc_id mytable1.fld3%type;
new_id mytable1.fld3%type;

begin

open mycur;
loop
fetch mycur into vc_id;
new_id := vc_id || '-OLD';
exit when mycur%notfound;
update mytable1
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;
/

any help is appreciated.