MERGE command and order of inserts updates

From: srini <srini.upp_at_gmail.com>
Date: 29 Jul 2005 02:18:34 -0700
Message-ID: <1122628714.462138.310020@g47g2000cwa.googlegroups.com>

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 Received on Fri Jul 29 2005 - 04:18:34 CDT