Using Oracle MERGE on same table based on condition

Question

I have created an Oracle sequence as below:

CREATE SEQUENCE TASK_ID_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;

I have a database table TASK as below:

TASK_ID nextval from TASK_ID_SEQ
TASK_DATE   SYSDATE
TASK_TYPE   <value fed from data>

I need to insert a fresh TASK record if the TASK_TYPE does not already appear in the TASK table for the date in TASK_DATE, otherwise ignore it.

Is the following syntax correct?

MERGE INTO TASK a
USING (SELECT b.task_date FROM TASK b) 
ON (a.task_type = b.task_type)
WHEN NOT MATCHED THEN
[INSERT INTO TASK]

Or should it be as:

MERGE INTO TASK a
USING (SELECT b.task_date FROM TASK b) 
ON (a.task_type = b.task_type)
WHEN MATCHED THEN
[]
WHEN MATCHED THEN
[INSERT INTO TASK]

Can I alternately use

MERGE INTO TASK USING (select 1 from DUAL) . . .

Please do suggest.

If it is only an insert then why not use an insert statement using select? — Jacob, May 09 '17 at 05:00
@user75ponic two reasons, one to accommodate update in future with minimal changes and secondly do you anticipate that Merge will be slower than insert? If not,then why not? — Dr. Debasish Jana, May 09 '17 at 05:04

APC · Accepted Answer · 2017-05-09T06:31:11.983

Your business rule indicates a match on DATE and TYPE. So there are two things wrong with your code:

the USING clause needs to select all the criteria necessary for determining a match.
the ON clause needs to test all the criteria necessary for determining a match.

Also, if you don't need to update existing records you can omit the WHEN MATCHED branch. So your MERGE statement should look something like this:

merge into task 
using ( 
    select date '2017-05-08' as dt, 'BATTLE' as typ from dual union all
    select date '2017-05-08' as dt, 'JUGGLE' as typ from dual union all
    select date '2017-05-08' as dt, 'PLOT' as typ from dual ) q
on (task.task_date = q.dt
    and task.task_type = q.typ)
when not matched then 
    insert values (task_id_seq.nextval, q.dt, q.typ)
/

A demo. Given this starting point ...

SQL> select * from task;

   TASK_ID TASK_DATE  TASK_TYPE
---------- ---------- ----------
         1 2017-05-06 CLEAN
         2 2017-05-06 BATTLE
         3 2017-05-06 JUGGLE
         4 2017-05-07 JUGGLE
         5 2017-05-07 CLEAN
         6 2017-05-07 NAP
         7 2017-05-08 BATTLE

7 rows selected.
SQL>

... the above MERGE should insert two rows (one row in the data source matches an existing row).

SQL> merge into task 
  2  using ( 
  3     select date '2017-05-08' as dt, 'BATTLE' as typ from dual union all
  4     select date '2017-05-08' as dt, 'JUGGLE' as typ from dual union all
  5     select date '2017-05-08' as dt, 'PLOT' as typ from dual ) q
  6  on (task.task_date = q.dt
  7     and task.task_type = q.typ)
  8  when not matched then 
  9     insert values (task_id_seq.nextval, q.dt, q.typ)
 10  /  

2 rows merged.

SQL> select * from task
  2  /

   TASK_ID TASK_DATE  TASK_TYPE
---------- ---------- ----------
         1 2017-05-06 CLEAN
         2 2017-05-06 BATTLE
         3 2017-05-06 JUGGLE
         4 2017-05-07 JUGGLE
         5 2017-05-07 CLEAN
         6 2017-05-07 NAP
         7 2017-05-08 BATTLE
         9 2017-05-08 JUGGLE
        10 2017-05-08 PLOT

9 rows selected.

SQL>

The data source is not entirely clear. So in the above example I generated a set of tasks using DUAL. If what you want is to create a new set of tasks for today from the set for yesterday the USING clause would look like this:

merge into task 
using ( 
    select trunc(sysdate) as dt, task_type as typ 
    from task
    where task_date = trunc(sysdate) - 1 ) q
on (task.task_date = q.dt
    and task.task_type = q.typ)
when not matched then 
    insert values (task_id_seq.nextval, q.dt, q.typ)
/

Using the same starting data as before this version inserts three rows:

SQL> select * from task;

   TASK_ID TASK_DATE  TASK_TYPE
---------- ---------- ----------
         1 2017-05-06 CLEAN
         2 2017-05-06 BATTLE
         3 2017-05-06 JUGGLE
         4 2017-05-07 JUGGLE
         5 2017-05-07 CLEAN
         6 2017-05-07 NAP
         7 2017-05-08 BATTLE
        11 2017-05-08 CLEAN
        12 2017-05-08 JUGGLE
        13 2017-05-08 NAP

10 rows selected.

SQL>

Using Oracle MERGE on same table based on condition

1 Answers1