How do I need to modify this MERGE statement, for a mass update of one field based on the value of a field in another table?

Question

As part of a bug fix for an ERP system I'm maintaining (Cleva), I need to mass update the SOR_CODE_PRODUCT2 field of the F_SIT_OBJET_RISQUE table with the PRI_REF_RISQUE field from the F_PRD_RISQUE table.

These two tables are linked together in correspondence with the following (simplified) data model:

I expected the following MERGE statement to work :

MERGE INTO F_SIT_OBJET_RISQUE 
USING (
  SELECT
      sor.SOR_IDENT,
      pri2.PRI_REF_RISQUE
  FROM F_SIT_OBJET_RISQUE sor
    JOIN F_PRD_RISQUE pri
       ON sor.SOR_REFOBJF = pri.PRI_REF_RISQUE
    JOIN F_PRD_RISQUE pri2
       ON pri.PRI_REF_RISQUE_C = pri2.PRI_REF_RISQUE
  WHERE pri2.PRI_REF_RISQUE IS NOT NULL
) SOURCEDATA ON (SOURCEDATA.SOR_IDENT = F_SIT_OBJET_RISQUE.SOR_IDENT)
WHEN MATCHED THEN UPDATE 
    SET F_SIT_OBJET_RISQUE.SOR_CODE_PRODUCT2 = SOURCEDATA.PRI_REF_RISQUE

It doesn't. It produces the following error:

SQL Error: ORA-30926: unable to get a stable set of rows in the source tables
30926. 00000 - "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.

The following MERGE statement, and many other variations I tried, all produce the same error :

MERGE INTO F_SIT_OBJET_RISQUE 
USING (
  SELECT 
      pas.PAS_IDENT,
      pol.POL_IDENT,
      sor.SOR_IDENT,
      pri.PRI_IDENT,
      pri2.PRI_IDENT "PRI_IDENT_2",
      pri2.PRI_REF_RISQUE
  FROM F_PRODUITASS pas
   JOIN F_POLICES pol
       ON pas.PAS_IDENT = pol.POL_PTRPASID
   JOIN F_SIT_OBJET_RISQUE sor
       ON pol.POL_IDENT = sor.SOR_PTRPOLID
   JOIN F_PRD_RISQUE pri
       ON sor.SOR_REFOBJF = pri.PRI_REF_RISQUE
      AND sor.SOR_TABLE = pri.PRI_TYPE_RISQUE
      AND pas.PAS_IDENT = pri.PRI_PTRPASIDENT
   JOIN F_PRD_RISQUE pri2
       ON pri.PRI_REF_RISQUE_C = pri2.PRI_REF_RISQUE
  WHERE pri2.PRI_REF_RISQUE IS NOT NULL
) SOURCEDATA ON (SOURCEDATA.SOR_IDENT = F_SIT_OBJET_RISQUE.SOR_IDENT)
WHEN MATCHED THEN UPDATE 
    SET F_SIT_OBJET_RISQUE.SOR_CODE_PRODUCT2 = SOURCEDATA.PRI_REF_RISQUE

Interestingly, the latter MERGE statement does work when I add the restriction AND pol.POL_NUMPOL = '5H00314' to my WHERE clause:

MERGE INTO F_SIT_OBJET_RISQUE 
USING (
  SELECT 
      pas.PAS_IDENT,
      pol.POL_IDENT,
      sor.SOR_IDENT,
      pri.PRI_IDENT,
      pri2.PRI_IDENT "PRI_IDENT_2",
      pri2.PRI_REF_RISQUE
  FROM F_PRODUITASS pas
   JOIN F_POLICES pol
       ON pas.PAS_IDENT = pol.POL_PTRPASID
   JOIN F_SIT_OBJET_RISQUE sor
       ON pol.POL_IDENT = sor.SOR_PTRPOLID
   JOIN F_PRD_RISQUE pri
       ON sor.SOR_REFOBJF = pri.PRI_REF_RISQUE
      AND sor.SOR_TABLE = pri.PRI_TYPE_RISQUE
      AND pas.PAS_IDENT = pri.PRI_PTRPASIDENT
   JOIN F_PRD_RISQUE pri2
       ON pri.PRI_REF_RISQUE_C = pri2.PRI_REF_RISQUE
  WHERE pri2.PRI_REF_RISQUE IS NOT NULL
    AND pol.POL_NUMPOL = '5H00314'
) SOURCEDATA ON (SOURCEDATA.SOR_IDENT = F_SIT_OBJET_RISQUE.SOR_IDENT)
WHEN MATCHED THEN UPDATE 
    SET F_SIT_OBJET_RISQUE.SOR_CODE_PRODUCT2 = SOURCEDATA.PRI_REF_RISQUE

Then, it does update the correct field with the correct value, and produces the following output :

3 rows merged.

For my mass update, I cannot use this restriction, however. The update needs to take place no matter what's the value of pol.POL_NUMPOL.

Any suggestions?

how about just adding `AND pol.POL_NUMPOL = pol.POL_NUMPOL`? — Migs Isip, Jan 04 '17 at 14:28
You need to ensure that in your source query, you end up with at most one row for the columns in the join clause. The error you are receiving indicates that for a given SOURCEDATA.SOR_IDENT, you have multiple rows. Which one should be used to update the target table? Oracle won't be able to tell (even if all of the rows have the same value), so you need to either make your join much more specific, or you need to ensure the source query only returns one row for each sor_ident. — Boneist, Jan 04 '17 at 14:31
@Boneist : Thanks. Your comment put my on the right track to solving my problem. I posted my solution as an answer, just in case someone else may learn from my mistake. — John Slegers, Jan 04 '17 at 15:25

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

I finally found what’s the problem.

The problem

The problem was with the join between F_SIT_OBJET_RISQUE and itself, which was missing at least one join criterion and therefore produced duplicate results.

The solution

Because the PRI_REF_RISQUE_C field from the first join with the F_SIT_OBJET_RISQUE and the PRI_REF_RISQUE from the second join with the F_SIT_OBJET_RISQUE contains the same value, I could just leave out this second join altogether :

So, in my MERGE statement, I just needed to remove the second join with the F_SIT_OBJET_RISQUE and replace references to the PRI_REF_RISQUE from the second join with the F_SIT_OBJET_RISQUE with references to PRI_REF_RISQUE_C field from the first join with the F_SIT_OBJET_RISQUE.

My final query:

MERGE INTO F_SIT_OBJET_RISQUE 
USING (
    SELECT 
      pas.PAS_IDENT,
      pol.POL_IDENT,
      sor.SOR_IDENT,
      pri.PRI_IDENT,
      pri.PRI_REF_RISQUE_C
    FROM
      F_SIT_OBJET_RISQUE sor
    JOIN F_POLICES pol
      ON sor.SOR_PTRPOLID = pol.POL_IDENT
    JOIN F_PRODUITASS pas
      ON pol.POL_PTRPASID = pas.PAS_IDENT
    JOIN F_PRD_RISQUE pri
      ON sor.SOR_REFOBJF = pri.PRI_REF_RISQUE
     AND sor.SOR_TABLE = pri.PRI_TYPE_RISQUE
     AND pas.PAS_IDENT = pri.PRI_PTRPASIDENT
) SOURCEDATA ON (SOURCEDATA.SOR_IDENT = F_SIT_OBJET_RISQUE.SOR_IDENT)
WHEN MATCHED THEN UPDATE 
    SET F_SIT_OBJET_RISQUE.SOR_CODE_PRODUCT2 = SOURCEDATA.PRI_REF_RISQUE_C;

This statement does not produce any more errors!

30,939 rows merged.

as an aside, why are you mixing old-style and ANSI style joins? You would do well to update the join between F_PRD_RISQUE and F_SIT_OBJET_RISQUE into ANSI syntax. — Boneist, Jan 04 '17 at 15:33
@Boneist : I updated my `MERGE` statement with the final version — John Slegers, Jan 04 '17 at 15:41

score 0 · Answer 2 · edited May 23 '17 at 11:53

ORA-30926 usually means that you have duplicates in one of the columns you're referencing in your USING clause. The reason pol.POL_NUMPOL = '5H00314' is working is because its referencing an exact row that has no duplicates.

Try putting a distinct so that duplicates will be removed before merging the data sets.

Check out some similar threads here in SO:

How do I need to modify this MERGE statement, for a mass update of one field based on the value of a field in another table?

2 Answers2

The problem

The solution