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