How to perform two different actions in merge when "not matched"

Question

I have these two tables

TABLE1
nrb   score  note  source
nrb1    500  abc   e1
nrb2    500  def   e1

and

TABLE2
nrb   score  note  source
nrb1    500  gls   e1
nrb3    500  dls   e1

There are three cases in which I want to perform different actions. I need to modify Table1 with new values:

1. WHEN MATCHED (by NRB) (f.ex. "nrb1") I want to keep values from table1 to this nrb, so basicly: do nothing with this record

2. WHEN NOT MATCHED #1: there is nrb in table2 but not in table1 (e.g. 'nrb3') I want to insert the values for this nrb from table2 to table1

3. WHEN NOT MATCHED #2: if there is nrb in table1 but not in table2 (e.g 'nrb2') I want to change score in table1 from 500 to 5

So in the end it should look like this:

TABLE1
nrb   score  note  source
nrb1    500  abc   e1 [stays the same]
nrb2      5  def   e1 [score changed]
nrb3    500  dls   e1 [new record from table2]

I need to use Oracle's merge statement for this task but I don't know how to perform two different actions.

Answer 1

You can't do that in a single merge. The when not matched clause can only insert, not update, as the syntax diagram shows.

Similarly the when matched clause can only update, not insert. You can't mix and match between them.

You will have to have a separate update statement. You can either merge to do the first part:

merge into table1 t1
using (select * from table2) t2
on (t2.nrb = t1.nrb)
when not matched then insert (nrb, score, note, source)
  values (t2.nrb, t2.score, t2.note, t2.source);

or do the equivalent insert:

insert into table1 (nrb, score, note, source)
select t2.nrb, t2.score, t2.note, t2.source
from table2 t2
where not exists (
  select null
  from table1 t1
  where t1.nrb = t2.nrb
);

select * from table1;

NRB       SCORE NOT SO
---- ---------- --- --
nrb1        500 abc e1
nrb2        500 def e1
nrb3        500 dls e1

And then whichever of those you do, update where there is no match:

update table1 t1
set score = score/100 -- or fixed value 5; unclear which you need
where not exists (
  select null
  from table2 t2
  where t2.nrb = t1.nrb
);

select * from table1;

NRB       SCORE NOT SO
---- ---------- --- --
nrb1        500 abc e1
nrb2          5 def e1
nrb3        500 dls e1

If you really wanted to to you could do that update via a merge, but it makes the using clause more complicated - the subset of table1 that has no matcing record in table2 - so I don't see any benefit:

merge into table1 t1
using (
  select * from table1 t1
  where not exists (
    select null from table2 t2 where t2.nrb = t1.nrb)) t2
on (t2.nrb = t1.nrb)
when matched then update set t1.score = score/100;

Answer 2

You need to do two separate tasks using two different SQL. Because MERGE doesn't support UPDATE clause for WHEN NOT MATCHED condition.

• MERGE INSERT
• UPDATE WHERE NOT EXISTS

Setup:

SQL> CREATE TABLE t1(
  2  nrb VARCHAR2(10), score NUMBER, note VARCHAR2(10), SOURCE VARCHAR2(10)
  3  );

Table created.

SQL> INSERT INTO t1 VALUES('nrb1', 500, 'abc', 'e1');

1 row created.

SQL> INSERT INTO t1 VALUES('nrb2', 500, 'def', 'e1');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> CREATE TABLE t2(
  2  nrb VARCHAR2(10), score NUMBER, note VARCHAR2(10), SOURCE VARCHAR2(10)
  3  );

Table created.

SQL> INSERT INTO t2 VALUES('nrb1', 500, 'gls', 'e1');

1 row created.

SQL> INSERT INTO t2 VALUES('nrb3', 500, 'dls', 'e1');

1 row created.

SQL> COMMIT;

Commit complete.

MERGE INSERT: To insert unmatched rows

SQL> MERGE INTO t1
  2  USING t2
  3  ON (t1.nrb = t2.nrb)
  4  WHEN NOT MATCHED THEN
  5    INSERT
  6      (
  7        nrb,
  8        score,
  9        note,
 10        SOURCE
 11      )
 12      VALUES
 13      (
 14        t2.nrb,
 15        t2.score,
 16        t2.note,
 17        t2.SOURCE
 18      );

1 row merged.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM t1;

NRB             SCORE NOTE       SOURCE
---------- ---------- ---------- ----------
nrb1              500 abc        e1
nrb2              500 def        e1
nrb3              500 dls        e1

UPDATE statement: To update the non-matched rows with custom value

SQL> UPDATE t1
  2  SET    score = 5
  3  WHERE  NOT EXISTS (SELECT NULL
  4                     FROM   t2
  5                     WHERE  t1.nrb = t2.nrb);

1 row updated.

SQL>                    COMMIT;

Commit complete.

SQL> SELECT * FROM t1;

NRB             SCORE NOTE       SOURCE
---------- ---------- ---------- ----------
nrb1              500 abc        e1
nrb2                5 def        e1
nrb3              500 dls        e1

NOTE Instead of MERGE with only INSERT, you could simply write a single INSERT statement. Although, from 10g on wards, the The MATCHED and NOT MATCHED clauses are optional, so you could have single INSERT or single UPDATE statements.