Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table product_a(
  a_id varchar2(10)
  ,status varchar2(50)
  ,item_code varchar2(50)
);
insert into product_a(a_id,status,item_code) 
values('a','1','8');
1 rows affected

select *
from product_a;


A_ID
STATUS
ITEM_CODE


a
1
8


create table product_b(
  a_id varchar2(10)
  ,item_code varchar2(50)
);
insert into product_b(a_id,item_code) 
values('bb','8');
1 rows affected

select *
from product_b;


A_ID
ITEM_CODE


bb
8


select
    t.a_id as pa_id,
    t.status as pa_status,
    s.a_id as pb_id  
from
    product_a t,
    product_b s  
where
    t.item_code = s.item_code


PA_ID
PA_STATUS
PB_ID


a
1
bb


update
        (
            select
                t.a_id as pa_id,
                t.status as pa_status,
                s.a_id as pb_id  
            from
                product_a t,
                product_b s  
            where
                t.item_code = s.item_code
        ) 
    set
        pa_id = pb_id,
        pa_status = 'Y'; 
ORA-01779: cannot modify a column which maps to a non key-preserved table