Oracle MERGE - if not matched then update if condition passes

Question

I need to merge some values into a table, updating a field when a row with the specified key already exists, or inserting a new row if it doesn't exist.

This is my table:

profiles(name, surname, active);

where:

name    VARCHAR2(30)
surname VARCHAR2(30)
active  NUMBER(1)

name and surname -> composite primary key

I'm using this query:

MERGE INTO profiles USING (
    SELECT
        'Mark' myName,
        'Zibi' mySurname,
        '1'    myActive
   FROM DUAL
) ON (
   name = myName
   AND surname = mySurname
)
WHEN MATCHED THEN
    UPDATE SET
        active = myActive
WHEN NOT MATCHED THEN
    INSERT (
        name,
        surname,
        active
    ) VALUES (
        myName,
        mySurname,
        myActive
    );

It works, but it updates a record even if active is already set to 1.

What I would like to do is something like this:

WHEN MATCHED THEN
    IF(active != myActive)
        UPDATE SET
            active = myActive
    ELSE
        RAISE CUSTOM EXCEPTION
WHEN NOT MATCHED THEN
    INSERT [...]

Is that possible? AFAIK I cannot put an if like this into a MERGE statement, so how could it be done?

Answer 1

Using PL/SQL to Run a Conditional Merge Operation

Edit: The original post asks how to process an existing set of data into an established table (named: PROFILES) through an approach that SQL or PL/SQL can solve it.

Edit Again: The last comment from OP was pretty subtle. If you don't have direct SQL access, then you will need a CURSOR, a driving query or some other construct to process each of the records your feeding in anyways. Many JDBC based middle-ware components also accept cursors as inputs. You could feed in all your data in one procedure call... take a look at REF CURSOR data types in PL/SQL. If that is the case, this solution can still help.

Using a composite join key, update data in a target table based on multiple criteria:

1. INSERT source data if it does not exist already.
2. Toggle or UPDATE a status value if the person identifier (name + surname) exists.
3. If person already exists in the target table and has an 'active' status already, skip it.

Sample Data

I named my tables slightly different and modified the column name "name" which is a reserved sql/plsql keyword... to prevent any possible future conflicts.

The sample data insert statements (DML):

*For clarity: The names in the test schema are not an exact match to the OP. STACK_PROFILES = PROFILES and STACK_PROFILE_MERGE_SOURCE represents "some source"... this could have been an xml feed, a csv text file, etc.etc.

from: load_profile_data.sql...

CREATE TABLE "STACK_PROFILES" ( "PROFILE_NAME" VARCHAR2(40), "SURNAME" VARCHAR2(40), "ACTIVE" NUMBER(1,0), CONSTRAINT "STACK_PROFILES_PK" PRIMARY KEY ("PROFILE_NAME", "SURNAME") ENABLE )

INSERT INTO STACK_PROFILES (profile_name, surname, active) VALUES ('LOIS' , 'LAINE', 0); INSERT INTO STACK_PROFILES (profile_name, surname, active) VALUES ('MARTIN', 'SHORT', 1); INSERT INTO STACK_PROFILES (profile_name, surname, active) VALUES ('ROBIN' , 'WILLIAMS', 0); INSERT INTO STACK_PROFILES (profile_name, surname, active) VALUES ('GRACE' , 'HOPPER', 0); INSERT INTO STACK_PROFILES (profile_name, surname, active) VALUES ('LOIS' , 'LAINE-KENT', 0);

commit; ...

CREATE TABLE "STACK_PROFILE_MERGE_SOURCE" ( "PROFILE_NAME" VARCHAR2(40), "SURNAME" VARCHAR2(40), CONSTRAINT "STACK_PROFILE_MERGE_SOURCE_PK" PRIMARY KEY ("PROFILE_NAME", "SURNAME") ENABLE ) /

INSERT INTO STACK_PROFILE_MERGE_SOURCE (profile_name, surname) VALUES ('BRUCE' , 'WAYNE'); INSERT INTO STACK_PROFILE_MERGE_SOURCE (profile_name, surname) VALUES ('SPONGE' , 'ROBERT'); INSERT INTO STACK_PROFILE_MERGE_SOURCE (profile_name, surname) VALUES ('CLARK' , 'KENT'); INSERT INTO STACK_PROFILE_MERGE_SOURCE (profile_name, surname) VALUES ('LOIS' , 'LAINE'); INSERT INTO STACK_PROFILE_MERGE_SOURCE (profile_name, surname) VALUES ('MARTIN' , 'SHORT'); INSERT INTO STACK_PROFILE_MERGE_SOURCE (profile_name, surname) VALUES ('DAMON' , 'WAYANS'); INSERT INTO STACK_PROFILE_MERGE_SOURCE (profile_name, surname) VALUES ('ROBIN' , 'WILLIAMS'); INSERT INTO STACK_PROFILE_MERGE_SOURCE (profile_name, surname) VALUES ('BRUCE' , 'WILLIS'); INSERT INTO STACK_PROFILE_MERGE_SOURCE (profile_name, surname) VALUES ('DENNIS' , 'HOPPER'); INSERT INTO STACK_PROFILE_MERGE_SOURCE (profile_name, surname) VALUES ('WHOOPI' , 'GOLDBERG'); INSERT INTO STACK_PROFILE_MERGE_SOURCE (profile_name, surname) VALUES ('GRACE' , 'HOPPER'); INSERT INTO STACK_PROFILE_MERGE_SOURCE (profile_name, surname) VALUES ('JERI' , 'RYAN');

Test Cases

It's helpful to understand the requirements presented. Writing up a few test cases gets us closer.

For test cases 1 and 2...

For test cases 3 and 4...

The PL/SQL Source Code

There is a simpler way to apply additional conditional logic through a SQL-merge like function. The PL/SQL Anonymous block following uses outer join syntax to identify records to be inserted vs. updated. The third category (active and already present in the target table) is also observed as the cursor processing loop skips records of that definition.

The processing loop and cursor

We use the FOR UPDATE and WHERE CURRENT OF syntax in the dml operations because the state of data referenced within this query changes during the lifespan of its use.

 declare
    c_default_status_active   constant number:= 1;
    c_status_inactive         constant number:= 0;

    cursor profile_cur is
       select sp.profile_name as target_name, 
              sp.surname as target_surname, sp.active as original_status,
              spm.profile_name as source_name, spm.surname as source_surname

         from stack_profiles sp, stack_profile_merge_source spm
        where spm.profile_name = sp.profile_name(+)
          and spm.surname = sp.surname(+)
        order by spm.profile_name asc nulls last, 
          spm.surname asc
          for update of sp.profile_name, sp.surname, sp.active;

        v_rec_profile  profile_cur%ROWTYPE;

     begin

      open profile_cur;
     fetch profile_cur into v_rec_profile;

     while profile_cur%found loop
       -- insert condition (no match in outer join...)
       if v_rec_profile.original_status is null
       then
       insert into stack_profiles (profile_name, surname, active)
       values (v_rec_profile.source_name, v_rec_profile.source_surname, 
           c_default_status_active);

       elsif
       -- flip status from inactive to active for existing but 
       -- inactive records.
       v_rec_profile.original_status = c_status_inactive then
       update stack_profiles
          set active = c_default_status_active
        where current of profile_cur;
          end if;

      fetch profile_cur into v_rec_profile;
      end loop;
      close profile_cur;

commit;

end;

Discussion

I have noted many different approaches to this type of problem. The specific approach used here is to demonstrate the concept involved. Results may vary depending on the database configuration, its usage and set up.