The error ORA-30926 as a result of a Merge

ORA-30926 error usually occurs when performing merge operations, and typically we leave something descolocados as the description of it does not give too much information about what is happening: 
ORA-30926: unable to get a stable set of rows in the source tables.

Normally this error occurs when the merge operation to target a row to be updated on it more than one row in the table source. As the engine does not know what to choose register returns an error. It is a problem of duplication in the origin table.

Example:

  • We have: 
    TABLA_ORIGEN with the values 
    ID Description 
    1 'The first value' 
    1 'The value with duplicate id' 
    2 'Another value'

    And with the values TABLA_DESTINO 
    ID Description 
    1 'Value to update' 
    2 'This will not trouble' 
    3 'This remains the same' 

  • We want to do a merge using the following sentence:

    MERGE into TABLA_DESTINO dest
    USING TABLA_ORIGEN ori
    ON (dest.ID = ori.ID)
    WHEN MATCHED THEN UPDATE SET a.Description = b.Description;

    With this data we get the following error on TABLA_ORIGEN: 
    ORA-30926: Failed to get a stable set of rows in the tables by

  • Given this error we have 3 options:

        1 -. Eliminating duplicate records from the source table:

                  DELETE FROM TABLA_ORIGEN WHERE id = 1 AND Description = 'The value with duplicate id' 

2 -. Review the key by which we join in the merge:

If you also use the Description field on the link and there will only be zero or one record for each origin destination: 

MERGE into TABLA_DESTINO dest
 
USING TABLA_ORIGEN ori
 
ON (dest.ID =ori.ID AND dest.Descripcion ori. Description)
WHEN MATCHED THEN UPDATE SET a.Description = b.Description;

(This example does not make much sense because the table are only those two fields, and they join you will not find matches)

3 -. Used instead of a MERGE UPDATE with JOIN and HINT /*+BYPASS_UJVC*/ skip to the validation engine, and cross your fingers:

UPDATE /*+BYPASS_UJVC*/ 
 (SELECT ori.ID ori_ID,
 
             ori.Descripcion ori_Descripcion,
 
             dest.ID dest_ID,
 
             dest.Description dest_Description
 
  FROM TABLA_ORIGEN ori, dest TABLA_DESTINO
 
  WHERE ori.ID = dest.ID)
 
SET dest_Description = ori_Description;

Obviously the most recommended are the first or second, depending on the situation.