Given a source table:

    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;

--------
binary_path hostname flag change_column
java         b      open       DMZ
apache       c      open       drn
NEW          NEW    open       NEW

And a destination table:

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;

------
binary_path hostname change_column flag
python      a         drn           null
java        b         drn           null
apache      c         drn           null
spark       d         drn           null

The primary key of both tables is the combination (binary_path,hostname) . I want to merge into destination the changes of source_after.

These should be:

1. If the primary key in destination is present in source_after, I want to update change_column in destination with the value of source_after.
2. If the primary key in destination is not present in source_after, I want to mark the flag column as closed.
3. If the primary key in source_after is not present in destination, I want to insert the row present in source_after which is not present in destination.

I have tried this:

merge into destination d
using (select * from source_after) s on (d.hostname = s.hostname and d.binary_path = s.binary_path)
when matched then update
set 
d.change_column = s.change_column,
d.flag = s.flag
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)
;

binary_path hostname change_column flag
    python  a   drn null
    java    b   DMZ open
    apache  c   drn open
    spark   d   drn null
    NEW    NEW  NEW open

It solves problem 1 and 3 , but not problem 2 which is marking the column flag as closed.

CodePudding user response：

If I understood you correctly, that won't work - not in a single statement.

• If something MATCHES, you can UPDATE it
• If there's NO MATCH, you can INSERT it
• You can't combine NO MATCH with UPDATE, which means that you'll have to write two statements

CodePudding user response：

You can use a FULL OUTER JOIN in the USING clause and correlate on the ROWID pseudo-column for the destination between the USING clause and the target of the MERGE:

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);

Which, for the sample data, changes the destination table to:

fiddle

CodePudding user response：

merge into destination d
using (
    select s.binary_path, s.hostname, s.change_column, s.flag from source_after s
    union all
    select d.binary_path, d.hostname, d.change_column, 'closed' from destination d
    where not exists(select 1 from source_after s where s.binary_path = d.binary_path and s.hostname = d.hostname)
) s 
on (d.hostname = s.hostname and d.binary_path = s.binary_path)
when matched then update
set 
    d.change_column = s.change_column, 
    d.flag = s.flag
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)
;