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