two way comparison in merge statement

Question

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:

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.
If the primary key in destination is not present in source_after, I want to mark the flag column as closed.
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.

Oracle does not support the `MATCHED BY TARGET`/`MATCHED BY SOURCE` syntax in a `MERGE` statement so you cannot do it in a single statement. — MT0, Jan 06 '23 at 10:58
You can do this, but you'd have to do it by working out the data set in the source query (e.g. do a full outer join between your destination and source_after tables). — Boneist, Jan 06 '23 at 11:02

score 1 · Answer 1 · answered Jan 06 '23 at 10:54

1

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

answered Jan 06 '23 at 10:54

Littlefoot

131,892
15
35
57

You can do it by changing your source: (https://dbfiddle.uk/eZkofVur) 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 Result: python a drn closed java b DMZ open apache c drn open spark d drn closed NEW NEW NEW open – p3consulting Jan 06 '23 at 11:14
The result is exactly the same as the original merge followed by a separate update to match condition 2: 5 rows where python and spark are flagged as 'closed' instead of null in the original merge. – p3consulting Jan 06 '23 at 11:41

score 1 · Accepted Answer · answered Jan 06 '23 at 11:46

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:

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

fiddle

this is neat. I need to digest the pseudo-column trick. Did not understand — moth, Jan 06 '23 at 13:41
@moth `ROWID` is effectively a pointer directly to the row and by correlating on it the SQL engine does not need to search the `destination` table for the row twice as you know exactly where the row is from the first time the table is queried. — MT0, Jan 06 '23 at 13:51

score 1 · Answer 3 · answered Jan 06 '23 at 12:18

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

two way comparison in merge statement

3 Answers3