Using Oracle MERGE in a single table based on a condition
I created an Oracle sequence as shown below:
CREATE SEQUENCE TASK_ID_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
I have a database table TASK
as shown below:
TASK_ID nextval from TASK_ID_SEQ
TASK_DATE SYSDATE
TASK_TYPE <value fed from data>
I need to insert a new record TASK
if TASK_TYPE
not already showing in the table TASK
for the date at 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 like:
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 suggest.
source to share
Your business rule specifies a match on DATE and TYPE. So there are two things in the code:
- In the USING clause, you must select all the criteria required to determine the match.
- the ON clause must check all the criteria necessary to determine compliance.
Also, if you don't need to update existing records, you can omit the WHEN MATCHED clause. Therefore, 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)
/
Demonstration. 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>
... 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 source of the data is not entirely clear. So in the above example, I generated a set of tasks using DUAL. If you want to create a new set of tasks for today from the set for yesterday's USING proposal 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 raw data as before this version, three rows are inserted:
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>
source to share