By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table table1(empli_id, status) as (
select 100000, 'A' from dual union all
select 100001, 'I' from dual union all
select 100002, 'A' from dual union all
select 100003, 'A' from dual union all
select 100004, 'I' from dual );
5 rows affected
create table table2(grp, empli_id, has_docs) as (
select 'ABC', 100002, cast(null as varchar2(1)) from dual union all
select 'XYZ', 100002, null from dual union all
select 'ABC', 100001, null from dual union all
select 'ABC', 100003, null from dual union all
select 'XYZ', 100004, null from dual );
5 rows affected
merge into table2 tgt
using (select empli_id, count(case status when 'I' then 1 end) cnt_inactive
from table1 group by empli_id) src
on (tgt.empli_id = src.empli_id)
when matched then update set
has_docs = case cnt_inactive when 0 then 'Y' else 'N' end;
5 rows affected
select * from table2;
| GRP | EMPLI_ID | HAS_DOCS |
|---|---|---|
| ABC | 100002 | Y |
| XYZ | 100002 | Y |
| ABC | 100001 | N |
| ABC | 100003 | Y |
| XYZ | 100004 | N |