||Oracle Tips by Burleson
Oracle 10g 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.
Get the complete story:
To get the code instantly, click here:
Need an Oracle Mentor?
BEI is now offering personal mentors for Oracle DBAs where you can have an
Oracle expert right at your fingertips, anytime day or night. We work with
hundreds of Oracle databases every year, so we know exactly how to quickly
assist you with any Oracle question.
Why risk an unplanned outage? You can now get telephone access to Don
Burleson or any of his Oracle Certified DBAs with more than 20 years of
full-time IT experience. Click here for details: