MERGE is a DML operation that combines aspects of INSERT,
UPDATE and DELETE. A single MERGE statement can perform one, two, or all
three
activities conditionally. There is nothing that can be
performed by the MERGE statement that cannot be performed individually
by a combination of INSERT, UPDATE and DELETE operations. The power of
a MERGE statement is in being able to perform multiple activities in a
single pass. For ETL activities in particular, a MERGE might be able to
significantly improve performance on operations involving a large amount
of data. In order to perform MERGE operations, you must have the neccesary
rights. There is no MERGE privilege – so you need, SELECT, UPDATE, and
DELETE privileges on the appropriate tables in order to perform MERGE operations.
The syntax for the MERGE statement is:
MERGE INTO dest_table tab_alias1
USING (source_expr) tab_alias2
ON (join_condition)
WHEN MATCHED THEN
UPDATE SET
col1 = val1,
col2 = val2
DELETE WHERE (del_cond)
WHEN NOT MATCHED THEN
INSERT (col_list)
VALUES (col_values)
- dest_table – The table for which rows will be inserted, updated, or deleted
- source_expr – The source of row data for the MERGE, this can be a table, view, or subquery.
- join_condition – The condition which is evaluated for each row.
- del_cond – Delete row if this condition is met.
- WHEN MATCHED – The operation in this clause will be performed when join_condition evaluates to TRUE.
- WHEN NOT MATCHED -- The operation in this clause will be performed when join_condition evaluates to FALSE.
ids match between the two tables, the backup EMP_ID record is updated to match all the current values in the primary table. Where the employee ID
does not exist in the backup, it is inserted directly from the primary.
MERGE INTO employees_bkup empb
USING (SELECT * FROM employees) emp
ON (empb.emp_id = emp.emp_id)
WHEN MATCHED THEN
UPDATE SET
empb.afl_id = emp.afl_id,
empb.emp_first = emp.emp_first,
empb.emp_last = emp.emp_last,
empb.emp_job = emp.emp_job,
empb.emp_supervisor = emp.emp_supervisor,
empb.salary = emp.salary,
empb.start_date = emp.start_date
WHEN NOT MATCHED THEN
INSERT VALUES (emp.emp_id, emp.afl_id, emp.emp_first,
emp.emp_last,
emp.emp_job, emp.emp_supervisor, emp.salary, emp.start_date);
A MERGE statement cannot alter the column that is referenced in the join condition of the ON clause.
