Oracle SQL Plus Pocket Reference, 2nd Edition

Merging Data (Oracle9i)

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 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.

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 the ON clause evaluates to TRUE, the row is considered to be an update to the COURSE_HOURS column. Otherwise, the row is considered a new ...