file,10) full (contracts_file) */ *
from contracts_file ))) f
d.contract_id = f.contract_id
update set desc
init_val_loc_curr = f.init_val_loc_curr,
init_val_adj_amt = f.init_val_adj_amt
insert values ( f.contract_id,
So there we have it - our complex ETL
function all contained within a single Oracle MERGE statement. No
separate SQL*Loader phase, no staging tables, and all
piped through and loaded in parallel.
Enhanced Merge Functionality
In Oracle Database 10g, the MERGE statement has
been extended to cover a larger variety of complex and conditional
data transformations, allowing faster loading of large volumes of
You should use the MERGE statement to select
rows from one or more sources for insert or update of one or more
tables. The decision to perform an insert or update is based on
conditions specified by you.
The MERGE statement is designed to combine
multiple operations to reduce the complexity of mixed insert and
update operations. MERGE allows you to avoid multiple INSERT,
UPDATE, and DELETE DML statements by combining the operations into a
single statement. MERGE is what is known as a deterministic
statement. That is, you can only update the same row of the target
table once in the same MERGE statement.
Since MERGE combines INSERT and UPDATE
operations, you must have the INSERT and UPDATE object privileges on
the target table, and of course, the SELECT object privilege on the
source table. If you need to specify the DELETE clause of the
merge_update_clause, then you must also have the DELETE object
privilege on the target table.
The syntax of the MERGE statement is:
MATCHED THEN UPDATE SET [<column> = [<expr>|DEFAULT][,]]<where_clause>
MATCHED THEN INSERT ( <column> [,])
The clauses in the MERGE statement have the
INTO Clause - The INTO clause is used to
specify the target table into which you are inserting or updating.
USING Clause - The USING clause specifies the
source of the data to be updated or inserted. The source for a MERGE
statement can be a table, view, or the result of a subquery.
ON Clause - The ON clause specifies the
condition that the MERGE operation uses to determine whether it
updates or inserts. When the search condition evaluates to true,
Oracle updates the row in the target table with corresponding data
from the MERGE source. If no rows satisfy the condition, then Oracle
inserts the row into the target table based on the corresponding
MERGE source row.
merge_update_clause - The merge_update_clause
is used to specify the update column values of the target table.
Oracle performs the specified update if the condition of the ON
clause is true. As with any normal update, when the update clause is
executed, all update triggers defined on the target table are fired.
where_clause - You must specify the where_clause
if you want Oracle to execute the update operation only if the
specified condition is true. The WHERE condition can apply to either
the data source or the target table. If the condition is false, the
update operation is skipped when merging the row into the target
You can specify the DELETE where_clause to
clean up data in a table while the MERGE statement is populating or
updating it. The only rows affected by the delete clause of the
MERGE statement are those rows in the target table that are updated
by the merge operation.
This means the DELETE WHERE condition evaluates
the updated value, not the original value of the row. Even if a row
of the target table satisfies the DELETE condition but is not
included in the data set from the join defined by the MERGE's ON
clause, then it is not deleted. If the MERGE statement deletes a
row, any delete triggers defined on the target table will be
activated for each row deletion.
merge_insert_clause – The WHERE clause can be
specified by itself or in concert with the merge_insert_clause. If
both are specified, then the order of the clauses is not important.
View Update Restrictions
To specify the values used for insert
operations the merge_insert_clause is used. The MERGE statement uses
the merge_insert_clause when the condition of the ON clause is
false. As with any normal insert, if the insert clause is executed,
all insert triggers defined on the target table are fired. If the
column list after the INSERT keyword is left out, the number of
columns in the target table must match the number of values in the
If you wish to insert all of the MERGE source
rows into the table, you should use what is known as a "constant
filter predicate" in the ON clause. An example of a constant filter
predicate would be:
A predicate such as the one shown is recognized
by Oracle as a special case, and Oracle makes an unconditional
insert of all source rows into the table. The benefit of this
approach over just omitting the merge_update_clause, Is that Oracle
still must perform a join if the merge_update_clause is left out,
while with a constant filter predicate, no join is performed.
You would specify the where_clause when you
want Oracle to execute the insert operation only if the specified
condition is true. The condition can refer only to the MERGE data
source. Oracle will skip the insert operation for all rows where the
condition evaluates to false.
You can specify the where_clause by itself or
with the merge_update_clause. If both are specified, then they can
be in either order.
n Example Merge
The following example is taken from the Oracle
documentation for Oracle Database 10g. The example creates a bonuses
table in the sample schema oe with a default bonus of 100. It then
inserts into the bonuses table all employees who made sales, based
on the sales_rep_id column of the oe.orders table. Finally, the
human resources manager decides that employees with a salary of
$8000 or less should receive a bonus. Those who have not made sales
get a bonus of 1% of their salary. Those who already made sales get
an increase in their bonus equal to 1% of their salary. The MERGE
statement implements these changes in one step:
TABLE bonuses (employee_id NUMBER, bonus NUMBER DEFAULT 100);
e.employee_id FROM employees e, orders o
(SELECT employee_id, salary, department_id FROM employees
MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
WHERE (S.salary > 8000)
MATCHED THEN INSERT (D.employee_id, D.bonus)
As you can see, the example uses some fairly
complex logic but manages to encapsulate the entire series of
inserts and updates into a single SQL MERGE statement.