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: 

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.