Lorem ipsum dolor prumus versus lorem.

Forex & Stock

Lorem ipsum dolor prumus versus lorem.

Lorem ipsum dolor prumus versus lorem.

MERGE

459/499

error_logging_clause ::=

Description of error_logging_clause.gif follows
Description of the illustration error_logging_clause.gif

Semantics

INTO Clause

Use the INTO clause to specify the target table or view you are updating or inserting into. In order to merge data into a view, the view must be updatable. Refer to "Notes on Updatable Views" for more information.

USING Clause

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

ON Clause

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.

Restrictions on the ON Clause 

Oracle Database does not implement fine-grained access control during MERGE statements. If you are using the fine-grained access control feature on the target table or tables, then use equivalent INSERT and UPDATE statements instead of MERGE to avoid error messages and to ensure correct access control.

merge_update_clause

The merge_update_clause 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 the where_clause if you want the database to execute the update operation only if the specified condition is true. The condition can refer to either the data source or the target table. If the condition is not true, then the database skips the update operation when merging the row into the table.

Specify the DELETE where_clause to clean up data in a table while populating or updating it. The only rows affected by this clause are those rows in the destination table that are updated by the merge operation. The DELETE WHERE 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 can specify this clause by itself or with the merge_insert_clause . If you specify both, then they can be in either order.

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

  • You cannot update a column that is referenced in the ON condition clause.

  • You cannot specify DEFAULT when updating a view.

merge_insert_clause

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

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

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

You can specify this clause by itself or with the merge_update_clause . If you specify both, then they can be in either order.

Restriction on Merging into a View  You cannot specify DEFAULT when updating a view.

error_logging_clause

Theerror_logging_clausehas the same behavior in a MERGE statement as in an INSERT statement. Refer to the INSERT statement error_logging_clause for more information.


Category: Spread

Similar articles: