Using Oracle MERGE on same table based on condition
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 ) qon (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 BATTLE7 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 PLOT9 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 ) qon (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 NAP10 rows selected.SQL>