MERGE

SEMANTICS

INTO Clause

Use the INTO clause to specify the target table or view into which you are updating or inserting. To combine data into one view, the view must be updatable. See “Notes on updatable views” for more information.

Restriction on target views You cannot specify a target view in which a INSTEAD OF trigger is defined.

clause

USING

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

clause

ON

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

ON Clause

Restrictions In previous versions of Oracle Database, when an Oracle Virtual Private Database policy was created in an application that included the MERGE INTO statement, the

MERGE INTO statement was prevented with an error ORA-28132: Merge into syntax does not support security policies, due to the presence of the Virtual Private Database policy. Starting with Oracle Database 11g Release 2 (11.2.0.2), you can create application policies that include MERGE INTO operations. To do this, in the DBMS_RLS. ADD_POLICY statement_types parameter, include the INSERT, UPDATE, and DELETE statements, or simply omit the statement_types parameter altogether. See the Oracle Database Security Guide for more information on applying policies to specific types of SQL statements.

merge_update_clause

The merge_update_clause specifies the new column values for the destination table. Oracle performs this update if the condition of the ON clause is true. If the refresh clause is executed, all refresh triggers defined in the destination table are triggered.

Specify the where_clause if you want the database to run the upgrade operation only if the specified condition is true. The condition can refer to the data source or the destination table. If the condition is not true, the database ignores the refresh operation when merging the row into the table.

Specify the DELETE where_clause to clean up data in a table while you populate or update it. The only rows affected by this clause are the rows in the destination table that are updated by the merge operation. The DELETE WHERE condition evaluates the updated value, not the original value evaluated by the UPDATE SET … WHERE condition. If a row in the destination table meets the DELETE condition but is not included in the combination defined by the ON clause, it is not deleted. Any delete trigger defined in the destination table will be triggered for each row deletion.

You can specify this clause alone or with the merge_insert_clause. If you specify both, they can be in any order.

Restrictions on merge_update_clause This clause is subject to the following restrictions:

You cannot update a

• column referenced in the ON condition clause

.

• You cannot specify DEFAULT when updating a view.

merge_insert_clause

The merge_insert_clause specifies the values to insert into the destination table column if the condition in the ON clause is false. If the insert clause is executed, all insert triggers defined in the destination table are activated. If you omit the list of columns after the INSERT keyword, the number of columns in the destination table must match the number of values in the VALUES clause.

To insert all source rows into the table, you can use a constant filter predicate in the ON clause condition. An example of a constant filter predicate is ON (0=1). Oracle Database recognizes that predicate and performs an unconditional insertion of all source rows into the table. This approach is different from skipping the merge_update_clause. In that case, the database must still perform a join. With the constant filter predicate, no join is performed.

Specify the where_clause if you want Oracle Database to run the insert operation only if the specified condition is true. The condition can only refer to the data source table. Oracle Database ignores the insert operation for all rows for which the condition is not true.

You can specify this clause on its own or with the merge_update_clause. If you specify both, they can be in any order.

Restriction on merge_insert_clause You cannot specify DEFAULT when inserting into a view.

error_logging_clause

The error_logging_clause has the same behavior in a MERGE statement as in an INSERT statement. See the INSERT statement error_logging_clause for more information.