Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
create table source_after (
    binary_path varchar2(40),
    hostname varchar2(40),
    change_column varchar2(40),
    flag varchar2(20) default 'open'
    ); 
 insert all 
    into source_after (binary_path,hostname,change_column) values ('java','b','DMZ') 
    into source_after (binary_path,hostname,change_column) values ('apache','c','drn')
    into source_after (binary_path,hostname,change_column) values ('NEW','NEW','NEW')
    select * from dual;
3 rows affected

create table destination (
binary_path varchar2(40),
hostname varchar2(40),
change_column varchar2(40),
flag varchar2(20) 
);
insert all 
into destination (binary_path,hostname,change_column) values ('python','a','drn') 
into destination (binary_path,hostname,change_column) values ('java','b','drn') 
into destination (binary_path,hostname,change_column) values ('apache','c','drn')
into destination (binary_path,hostname,change_column) values ('spark','d','drn')
select * from dual;
4 rows affected

MERGE INTO destination d
USING (
  SELECT d.ROWID AS rid,
         s.*
  FROM   destination d
         FULL OUTER JOIN source_after s
         ON (d.hostname = s.hostname AND d.binary_path = s.binary_path)
) s
ON (s.rid = d.ROWID)
  WHEN MATCHED THEN
    UPDATE
    SET d.change_column = COALESCE(s.change_column, d.change_column),
        d.flag          = COALESCE(s.flag, 'closed')
  WHEN NOT MATCHED THEN
    INSERT (d.binary_path,d.hostname,d.change_column,d.flag)
    VALUES (s.binary_path,s.hostname,s.change_column,s.flag);
5 rows affected

SELECT * FROM destination;


BINARY_PATH
HOSTNAME
CHANGE_COLUMN
FLAG


python
a
drn
closed

java
b
DMZ
open

apache
c
drn
open

spark
d
drn
closed

NEW
NEW
NEW
open

BINARY_PATH	HOSTNAME	CHANGE_COLUMN	FLAG
python	a	drn	closed
java	b	DMZ	open
apache	c	drn	open
spark	d	drn	closed
NEW	NEW	NEW	open