The MERGE statement

The SQL statement MERGE (available from version 9i) provides the functionality of “INSERT or UPDATE” in a single statement.

The basic syntax is:

MERGE INTO <target>
USING <source>
ON (<join conditions>)
WHEN MATCHED THEN UPDATE <SET clause>
WHEN NOT MATCHED THEN INSERT (<column list>) VALUES (<expression list>);

where:

• <target> is the table that we’d like to insert new records into and/or update existing records in
• <source> is usually some SQL query (it may also be simply a table)

• <join conditions> specify the relationship between the source and the target – these conditions determine whether a row from the source exists in the target or not

Each row from the source is looked up in the target table, using the conditions specified in the ON clause. If the row exists in the target table, then the WHEN MATCHED clause is executed – the target row is updated. If the row does not exist in the target table, then the WHEN NOT MATCHED clause is executed – a new row is inserted into the target table.

Let’s take an example. An OPERATIONS_LOG table contains a record for each operation done in the system. Each record contains the ID of the employee that executed the operation and the execution time:

create table operations_log (
  employee_id    number not null,
  operation_time date   not null
);

An EMP_OPERATION_SUMMARY table contains one record for every employee, with the employee ID, the total number of operations done by the employee, and the time of the last operation of the employee:

create table emp_operation_summary (
  employee_id          number not null primary key,
  number_of_operations number not null,
  last_operation_time  date   not null
);

Now we need to update the EMP_OPERATION_SUMMARY table with the aggregated data from the OPERATIONS_LOG table, while adding new records for employees that do not exist there yet, and updating the records of employees that already exist there. Using the MERGE statement, this can be done easily:

merge into emp_operation_summary trgt
using (select employee_id,
              count(*) number_of_operations,
              max(operation_time) last_operation_time
       from   operations_log
       group  by employee_id) src
on (trgt.employee_id = src.employee_id)
when matched then
    update
    set    trgt.number_of_operations = trgt.number_of_operations + src.number_of_operations,
           trgt.last_operation_time  = src.last_operation_time
when not matched then
    insert
        (trgt.employee_id,
         trgt.number_of_operations,
         trgt.last_operation_time)
    values
        (src.employee_id,
         src.number_of_operations,
         src.last_operation_time);

 

The following restrictions apply to the MERGE statement:

 

• Each row in the target table may be updated at most once by a MERGE statement (that is, the join between the source and the target may not return the same row more than once). Violating this rule will raise the “ORA-30926: unable to get a stable set of rows in the source tables” error.
• Columns that appear in the ON clause cannot be updated in the WHEN MATCHED clause. Violating this rule will raise the “ORA-38104: Columns referenced in the ON Clause cannot be updated” error.

All the following options are available from version 10g.

You can define either one of the following:

• only the WHEN MATCHED clause – in this case rows from the source that already exist in the target will be updated in the target, but source rows that do not exist in the target will be ignored
• only the WHEN NOT MATCHED clause – in this case rows from the source that do not exist in the target will be inserted into the target, but source rows that already exist in the target will be ignored
• both WHEN MATCHED and WHEN NOT MATCHED clauses – in this case every source row will affect the target – by either an UPDATE or an INSERT, respectively

Each of the WHEN MATCHED and WHEN NOT MATCHED clauses may also have a WHERE clause, to restrict the UPDATE/INSERT operation to specific conditions. Let’s enhance the previous example, and create new records only for employees that don’t exist in the EMP_OPERATION_SUMMARY table yet and that have more than 2 records in the OPERATIONS_LOG table:

merge into emp_operation_summary trgt
using (select employee_id,
              count(*) number_of_operations,
              max(operation_time) last_operation_time
       from   operations_log
       group  by employee_id) src
on (trgt.employee_id = src.employee_id)
when matched then
    update
    set    trgt.number_of_operations = trgt.number_of_operations + src.number_of_operations,
           trgt.last_operation_time  = src.last_operation_time
when not matched then
    insert
        (trgt.employee_id,
         trgt.number_of_operations,
         trgt.last_operation_time)
    values
        (src.employee_id,
         src.number_of_operations,
         src.last_operation_time) 
    where src.number_of_operations > 2;

The WHEN MATCHED clause may also include a DELETE WHERE clause, to delete rows that were updated during this MERGE statement and that follow the specified condition. Note that the condition in this DELETE clause refers to the updated values, not the original ones.