MERGE command and order of inserts updates

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

srini - 29 Jul 2005 10:18 GMT

Hi,

I get an Unique constraint violation for the following merge command

MERGE INTO destination D
USING ( SELECT * FROM DEP_ACCT
    where
    UPDT_TS BETWEEN TO_DATE('&1','MM/DD/YYYY HH24:MI:SS') AND
TO_DATE('&2','MM/DD/YYYY HH24:MI:SS')

) S
ON (S.DEP_ACCT_ID = D.DEP_ACCT_ID )
WHEN MATCHED THEN
UPDATE
SET
        D.BNK_RTE_NB = S.BNK_RTE_NB,
        D.DEP_ACCT_NB = S.DEP_ACCT_NB,
WHEN NOT MATCHED THEN
INSERT (D.DEP_ACCT_ID,D.BNK_RTE_NB)
VALUES(S.DEP_ACCT_ID,S.BNK_RTE_NB,S.DEP_ACCT_NB).

A unique constraint index is defined on columns BNK_RTE_NB and
DEP_ACCT_NB on the source as well as the destination tables.

DEP_ACCT_ID is the primary key on source and destination tables.

Because of this constraint, it is important that updated records are
merged prior to insertion of new records, especially when the
combination of BNK_RTE_NB and DEP_ACCT_NB already exists in
destination.

Questions:

What is the order of records, that the MERGE command performs the
upserts ? Is it consistent can we rely on the order.

Will the order vary, depending on the number of records that the join
returns ?

If I want to use MERGE command, what is the workaround for my
problem(without reming the constraints, on destination) ?

Thanks in advance.
srini

Reply to this Message

Holger Baer - 29 Jul 2005 10:44 GMT

> Hi,
>
[quoted text clipped - 40 lines]
> Thanks in advance.
> srini

To control the order of records, you can include an order by in the select
statement - at least on 10g where i tested this.

But if that's going to help you in your specific case, I really can't tell.

HTH
Holger

Reply to this Message

srini - 29 Jul 2005 13:19 GMT

Thanks for the reply. I am using 9i, and MERGE command ignores the
order by clause.

Reply to this Message

Patrice Borne - 31 Jul 2005 21:42 GMT

Hi,

I think the way you are using the MERGE command is not correct, based
on a set perspective and that's where you have a problem. Avoid
thinking in terms or "orders of the rows are processed" and consider
the result you are trying to achieve in term of "sets".

First of all, what is the MERGE command doing?

It takes a result set from a query as an input that contains all the
rows you would like to see at the output, based on KEY fields. It then
merges (hence the name) the source set with the destination set, based
on a joining condition.

If the row already exists (based on the KEY usually, if not you can get
into troubles), you want to update one or more NON KEY fields. If the
row does not exist in the destination table, you want to insert the
full row.

So, as a summary (simplified):

MERGE into DESTINATION
USING (THE RESULT SET TO USE AS A SOURCE)
ON (JOIN CONDITIONS BETWEEN THE DESTINATION AND SOURCE)
WHEN MATCHED
UPDATE NON-KEY FIELDS
WHEN NOT MATCHED
INSERT A COMPLETE ROW

There are constraints obviously:

1/ The result set used as a source MUST be built so that the uniqueness
of the fields used in the joining clause is guaranteed. If not, the
MERGE will bomb, because there is no way for Oracle to know which row
should be commited at the output. Do not think in terms of "what row is
processed first", this doesn't make sense from a set perspective.

2/ the UPDATE cannot modify any KEY field of the destination table, for
obvious reasons. If you were allowed to modify the key fields, which
value should the MERGE command use in its comparison to join? The
values before or after? This doesn't make sense either from a set
perspective. Also, you could easily generate duplicate rows (based on
the KEY of the destination table)

3/ The INSERT command MUST be guaranteed to succeed, based on the JOIN
condition used in the ON clause.

Once you shift from a "row by row" approach to a "set" approach, all
these constraints make sense, the MERGE command becomes obvious, and
your remark saying that the "order by" clause doesn't change anything
makes sense too. The whole point of relational databases is to work on
"sets" and that's why the "order by" is usually irrelevant for bulk
processing.

Patrice Borne

Reply to this Message