New Features / Documentos / Soporte Técnico / Servicios / Tilsor
 
 

Oracle9i: Merge SQL statement

Creation Date: 20-MAR-2001
Last Revision Date: 06-MAY-2002

PURPOSE
This article is intended to introduce the MERGE SQL statement available in
Oracle9i.


SCOPE & APPLICATION
This article assumes the reader has an understanding of SQL.


RELATED DOCUMENTS
Oracle 9i SQL Reference


Oracle9i Merge:
===============

The MERGE statement introduced in Oracle9i allows a single SQL statement to
either insert or update a table conditionally by selecting rows from another
table or view, or using a subquery. If the row already exists, an update is
done, otherwise the row is inserted.

MERGE is useful in data warehousing environments where source data is being
loaded into a destination table and there may already be existing rows for a
particular key value.

The INTO clause specifies the target table to be updated or inserted into. The
USING clause specifies the source. This can be a table, view or a subquery. The
ON clause specifies the condition to be used to determine whether an insert or
update occurs. If now rows in the target table satisfy the condition in the ON
clause, the source data is inserted.

Any INSERT/UPDATE triggers that are defined on the source table will be
executed as with standard INSERT/UPDATE.

MERGE can be parallelized using parallel DML rules. The current restrictions
which apply to parallel UPDATE will apply to MERGE, namely that the MERGE will
only be parallelized if the destination table is partitioned. Each partition
will be INSERTED/UPDATED by only one slave.

Restrictions on the update clause:
- You cannot specify DEFAULT when updating a view.
- You cannot update a column that has been referenced in the ON condition
clause.

Example:
--------

Suppose you have a source and a destination table. You want to add the data to
the value of any existing rows and insert any new rows.

MERGE INTO tdest d
USING tsrc s
ON (s.srckey = d.destkey)
WHEN MATCHED THEN
UPDATE SET d.destdata = d.destdata + s.srcdata
WHEN NOT MATCHED THEN
INSERT (destkey,destdata) VALUES (srckey,srcdata)

Multiple inserts into the destination of the same key value from the source
table will be allowed. However, there is a restriction that multiple updates
to the same row in the destination table is not allowed. The following error
will be returned if this is attempted:

ORA-30926: unable to get a stable set of rows in the source tables
Cause: A stable set of rows could not be got because of large dml
activity or a non-deterministic where clause.
Action: Remove any non-deterministic where clauses and reissue the dml.

It is possible, though, to aggregate the results in an inline view in the USING
clause.


Examples:
---------

MERGE INTO tdest d
USING (SELECT srckey,sum(srcdata) sumsrc
FROM tsrc GROUP BY srckey) s
ON (d.destkey=s.srckey)
WHEN MATCHED THEN
UPDATE SET d.destdata=s.sumsrc + d.destdata
WHEN NOT MATCHED THEN
INSERT (destkey,destdata) VALUES (srckey,sumsrc);

MERGE INTO account a
USING (SELECT id, sum(balance) sum_balance FROM
transaction GROUP BY id) t
ON (a.id=t.id)
WHEN MATCHED THEN
UPDATE SET a.balance = a.balance + t.sum_balance
WHEN NOT MATCHED THEN
INSERT (a.id,a.balance) VALUES (t.id,t.sum_balance);


If you attempt to update a column that is also referenced in the ON condition
clause, you will receive an ORA-904 error:

ORA-00904: invalid column name
Cause: The column name entered is either missing or invalid.
Action: Enter a valid column name.

This is documented in [BUG:2124282].


References:
===========

[BUG:2124282] ORA-904 USING MERGE STATEMENT