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 product_a
    set
        a_id = (
            select product_b.a_id
            from product_b
            where product_a.item_code = product_b.item_code
        ) ,
        status = 'Y'; 
        
        
1 rows affected