SQL merge statement with multiple conditions

Question

I have a requirement with some business rules to implement on SQL (within a PL/SQL block): I need to evaluate such rules and according to the result perform the corresponding update, delete or insert into a target table.

My database model contains a "staging" and a "real" table. The real table stores records inserted in the past and the staging one contains "fresh" data coming from somewhere that needs to be merged into the real one.

Basically these are my business rules:

1. Delta between staging MINUS real --> Insert rows into the real
2. Delta between real MINUS staging--> Delete rows from the real
3. Rows which PK is the same but any other fields different: Update.

(Those "MINUS" will compare ALL the fields to get equality and distinguise the 3rd case)

I haven't figured out the way to accomplish such tasks without overlapping between rules by using a merge statement: Any suggestion for the merge structure? Is it possible to do it all together within the same merge?

Thank you!

Answer 1

If I understand you task correctly following code should do the job:

--drop table real;
--drop table stag;

create table real (
  id NUMBER,
  col1 NUMBER,
  col2 VARCHAR(10)
);

create table stag (
  id NUMBER,
  col1 NUMBER,
  col2 VARCHAR(10)
);

insert into real values (1, 1, 'a');
insert into real values (2, 2, 'b');
insert into real values (3, 3, 'c');
insert into real values (4, 4, 'd');
insert into real values (5, 5, 'e');
insert into real values (6, 6, 'f');
insert into real values (7, 6, 'g'); -- PK the same but at least one column different
insert into real values (8, 7, 'h'); -- PK the same but at least one column different
insert into real values (9, 9, 'i');
insert into real values (10, 10, 'j'); -- in real but not in stag

insert into stag values (1, 1, 'a');
insert into stag values (2, 2, 'b');
insert into stag values (3, 3, 'c');
insert into stag values (4, 4, 'd');
insert into stag values (5, 5, 'e');
insert into stag values (6, 6, 'f');
insert into stag values (7, 7, 'g'); -- PK the same but at least one column different
insert into stag values (8, 8, 'g'); -- PK the same but at least one column different
insert into stag values (9, 9, 'i');
insert into stag values (11, 11, 'k'); -- in stag but not in real

merge into real
     using (WITH w_to_change AS (
              select *
                from (select stag.*, 'I' as action from stag
                       minus
                      select real.*, 'I' as action from real
                     )
               union (select real.*, 'D' as action from real
                       minus 
                      select stag.*, 'D' as action from stag
                     )
            )
            , w_group AS (
              select id, max(action) as max_action
                from w_to_change
               group by id
            )
            select w_to_change.*
              from w_to_change
              join w_group
                on w_to_change.id = w_group.id
               and w_to_change.action = w_group.max_action
           ) tmp
   on (real.id = tmp.id)
 when matched then
   update set real.col1 = tmp.col1, real.col2 = tmp.col2
   delete where tmp.action = 'D'
 when not matched then
   insert (id, col1, col2) values (tmp.id, tmp.col1, tmp.col2);