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

You can do this by adding where condition either on source -using (---- subquery ---), to filter when matched command, or add where condition after when not matched.

In the following example, I will merge records from id 520 up to 530, at the same time I will not insert record where id =525

--------
merge into merchant_tmp2 dest
using (select * from merchant where id between 520 and 530) src
on(dest.id=src.id)
when matched then 
update set address=address ||' - updated'
when not matched then 
insert (ID,....)
values (src.ID,....)
where src.id <> 525;

ref:https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm#SQLRF01606

Answer 2

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.

Answer 3

It's better to use PL/SQL in such a case via the stored procedure or just by executing anonymous SQL block from a client side instead of single MERGE SQL statement.

Anonymous PL/SQL block may look like:

declare
  -- Parameters of query, initialization values  
  pName    profiles.name%type    := 'Mark';
  pSurname profiles.surname%type := 'Zibi';
  pActive  profiles.active%type  := 0;

  -- variable used for test against table
  vIsActiveInDb profiles.active%type;
begin

  select 
    max(profs.active) into vIsActiveInDb
  from 
    profiles profs
  where 
    profs.name = pName and profs.surname = pSurname
  ;

  if(vIsActiveInDb is null) then
    -- profile not found, create new one 
    insert into profiles(name, surname, active)
    values(pName, pSurname, pActive);

  elsif(vIsActiveInDb != pActive) then
    -- profile found, activity flag differs 
    update profiles set active = pActive 
    where name = pName and surname = pSurname;

  else
    -- profile found with same activity flag
    raise_application_error(
      -20001, -- custom error code from -20000 to -20999
      'Profile "'||pName||' '||pSurname||'" already exists with same activity flag'
    );  
  end if;

end;

SQLFiddle

There are two suggestions in code above:
1. (name, surname) pair is a primary key, so always selected single row or nothing;
2. active field can't be null (e.g. created with not null constraint).
Code would be a little bit more complicated if this suggestions fails. This variant may be found in this SQLFiddle.

I never used MyBatis but based on answer from your comment XML description for such query may look like that:

<update id="UpdateProfileActivity" parameterType="map" statementType="CALLABLE">   
  declare
    -- Parameters of query, initialization values
    pName    profiles.name%type    := #{piName,    mode=IN, jdbcType=VARCHAR};
    pSurname profiles.surname%type := #{piSurname, mode=IN, jdbcType=VARCHAR};
    pActive  profiles.active%type  := #{piActivity,mode=IN, jdbcType=NUMERIC};

    -- variable used for test against table
    vIsActiveInDb profiles.active%type;   begin

    select
      max(profs.active) into vIsActiveInDb
    from
      profiles profs
    where
      profs.name = pName and profs.surname = pSurname
    ;

    if(vIsActiveInDb is null) then
      -- profile not found, create new one
      insert into profiles(name, surname, active)
      values(pName, pSurname, pActive);

    elsif(vIsActiveInDb != pActive) then
      -- profile found, activity flag differs
      update profiles set active = pActive
      where name = pName and surname = pSurname;

    else
      -- profile found with same activity flag
      raise_application_error(
        -20001, -- custom error code from -20000 to -20999
        'Profile "'||pName||' '||pSurname||'" already exists with same activity flag'
      );
    end if;

  end; 
</update>

Answer 4

Ok, this is not good practice, i suppose, but since your ACTIVE column has type NUMBER(1) you can easily generate ORA-01438 exception by simply trying to update it's value to bigger one. For example, something like this will throw an exception, if new and old values of active are equal:

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 =  CASE WHEN active = myActive THEN 11 ELSE myActive END
WHEN NOT MATCHED THEN
    INSERT (
        name,
        surname,
        active
    ) VALUES (
        myName,
        mySurname,
        myActive
    );