ORA-30926 and MERGE

August 20, 2022

TL;DR: Your merge condition does not uniquely identify one row in the target table.

ORA-30926 and MERGE

If we ignore bugs that may happen, ORA-30296 comes from a logical error in the merge condition. One row from the source set should match one and only one in the target table, if it exists, in the target. If it matches more than one, the merge statement cannot work as intended, and you need to check what uniquely identifies a row.

This problem does not happen when the merge condition is the same as the primary key of the target table. But if you are doing some data cleansing or importing where the data comes from a system where people stuffed data in any available field, you may have to be creative with your merge condition. If your merge statement is complex and the data is messy, it is easy to introduce this error.

Also, it is easy to forget that the first load will succeed because the target table is empty. (“Why does it fail now?")

If you are in control of your data model, have an extra check to see if it has a logical flaw. Or it could be you need an additional predicate in your MERGE statement.