Merge Statement

Select rows from one or more sources for update or insertion into a table.

Syntax:

   MERGE [hint] INTO [schema.]table [@dblink] [t_alias]
     USING [schema.]table [t_alias] ON (condition)
       [WHEN MATCHED THEN UPDATE SET (column = {expr | DEFAULT}, column = {expr | DEFAULT},…)]
         [ WHERE condition ] [DELETE WHERE condition ]
           [ WHEN NOT MATCHED THEN INSERT (column, column,…) VALUES ( {expr | DEFAULT},{expr | DEFAULT},… ) ]
             [ WHERE condition ]
               [ LOG ERRORS [INTO [schema.]table] [(simple_expression)] [ REJECT LIMIT { integer | UNLIMITED } ] ]

Use the INTO clause to specify the target table or view. To merge data into a view, the view must be updatable.

Use the USING clause to specify the source of the data, this can be a table, view, or the result of a subquery.

Use the ON clause to specify the condition upon which the MERGE operation either updates or inserts. For each row in the target table for which the search condition is true, Oracle Database updates the row with corresponding data from the source table. If the condition is not true for any rows, then the database inserts into the target table based on the corresponding source table row.

WHEN MATCHED THEN UPDATE specifies the new column values of the target table. Oracle performs this update if the condition of the ON clause is true. If the update clause is executed, then all update triggers defined on the target table are activated.

Specify DELETE WHERE condition to clean up data in a table while populating or updating it. This will only affect rows in the destination table that are updated by the merge operation. The condition evaluates the updated value, not the original value that was evaluated by the UPDATE SET ... WHERE condition. If a row of the destination table meets the DELETE condition but is not included in the join defined by the ON clause, then it is not deleted. Any delete triggers defined on the target table will be activated for each row deletion.

You cannot update a column that is referenced in the ON condition clause.
You cannot specify DEFAULT when updating a view.

The VALUES clause can be replaced with a sub query. The VALUES keyword is required only when directly inserting data values, rather than using a subquery. (In some early Oracle versions, you will need to use the 'old style' join syntax in the subquery.)

Examples:

SQL> MERGE INTO bonuses B
   USING (SELECT employee_id, salary, department_id FROM employees
   WHERE department_id = 80) Sal
   ON (B.employee_id = Sal.employee_id)
   WHEN MATCHED THEN UPDATE SET B.bonus = B.bonus + Sal.salary*.01
     DELETE WHERE (Sal.salary > 50000)
   WHEN NOT MATCHED THEN INSERT (B.employee_id, B.bonus)
     VALUES (Sal.employee_id, Sal.salary*.01)
     WHERE (Sal.salary <= 50000);

"When debugging, novices insert corrective code; experts remove defective code" ~ Richard Pattis

Related Oracle Commands:

DELETE
EXPLAIN PLAN
INSERT
SELECT
TRUNCATE
UPDATE

 DBA_SNAPSHOTS        ALL_SNAPSHOTS        USER_SNAPSHOTS
 DBA_ALL_TABLES       ALL_ALL_TABLES       USER_ALL_TABLES
 DBA_TABLES           ALL_TABLES           USER_TABLES
     TAB
 DBA_VIEWS            ALL_VIEWS            USER_VIEWS
 DICTIONARY
 DICT_COLUMNS