Oracle SQL*Plus Pocket Reference, 3rd Edition

Merging Data (Oracle9i and Higher)

A common data-processing problem is the need to take some data, decide whether it represents a new row in a table or an update to an existing row, and then issue an INSERT or UPDATE statement as appropriate. In the past, this has always been at least a two-step process, requiring two round-trips to the database. New in Oracle9i Database is the MERGE statement, which makes the process of inserting or updating easier and more efficient than before.

The general form of the MERGE statement is as follows:

MERGE INTO  table
USING  data_source
ON (condition)
WHEN MATCHED THEN  update_clause
WHEN NOT MATCHED THEN  insert_clause;

In this syntax, data_source can be a table, view, or query. The condition in the ON clause is what Oracle looks at to determine whether a row represents an insert or an update to the target table.

Tip

Beginning in Oracle Database 10g, you no longer need to include both the WHEN MATCHED and WHEN NOT MATCHED clauses.

Here is an example of a MERGE statement:

MERGE INTO course c
USING (SELECT course_name, period, course_hours
       FROM course_updates) cu
ON (c.course_name = cu.course_name
    AND c.period = cu.period)
WHEN MATCHED THEN
   UPDATE
   SET c.course_hours = cu.course_hours
WHEN NOT MATCHED THEN
   INSERT (c.course_name, c.period,
           c.course_hours)
   VALUES (cu.course_name, cu.period,
           cu.course_hours);

When processing this statement, Oracle reads each row from the query in the USING clause and looks at the condition in the ON clause. If the condition in ...