What happens when a MERGE source contains two rows matching the same target row?

## The rule A MERGE statement may act on any given target row **at most once**. If the ON condition makes two or more source rows match the same target row, the statement is in error — the standard calls this a cardinality violation — and the engine aborts it rather than choosing a winner. This is deliberate: "whichever source row the engine happened to process last wins" would make the result depend on physical row order, which SQL refuses to expose. ## What it looks like in practice A daily feed arrives with two rows for account 42 — an intraday correction followed by the end-of-day figure: ```sql MERGE INTO account t USING incoming_feed s ON (t.account_id = s.account_id) WHEN MATCHED THEN UPDATE SET balance = s.balance WHEN NOT MATCHED THEN INSERT (account_id, balance) VALUES (s.account_id, s.balance); ``` If account 42 already exists in `account`, both feed rows are matched against the same target row and the statement fails. Oracle reports `ORA-30926: unable to get a stable set of rows in the source tables`; other engines phrase it as the MERGE attempting to update or delete the same row more than once. The important part is the shape of the failure: it is loud, it is at statement level, and nothing is applied. ## The asymmetry people miss The rule protects the **target** row, not the source row. One source row that matches five target rows is perfectly legal — each of those five target rows is affected exactly once, so all five are updated with the same values. That is often exactly what you want when merging a lookup value onto many detail rows, and it is why the error message talking about "the source" confuses people the first time. ## The duplicate also bites the insert arm Suppose account 42 does *not* exist yet. Now neither feed row matches anything, so both are classified not matched and both flow into `WHEN NOT MATCHED THEN INSERT`. Classification happens against the target as it was before the statement, so the second row does not "see" the row the first one inserted. You end up with two rows for the same key — or, if the target has a unique constraint on `account_id`, a unique-constraint violation instead of the tidy cardinality error. Either way, duplicate source keys are the defect. ## The fix: shape the source The repair always lives in the `USING` query. Reduce the source to exactly one row per match key, using a rule you can defend: ```sql MERGE INTO account t USING ( SELECT account_id, MAX(balance) AS balance FROM incoming_feed GROUP BY account_id ) s ON (t.account_id = s.account_id) WHEN MATCHED THEN UPDATE SET balance = s.balance WHEN NOT MATCHED THEN INSERT (account_id, balance) VALUES (s.account_id, s.balance); ``` Aggregation is the simplest form, but it is only correct when the aggregate *is* the business rule. For "last write wins" feeds the honest rule is a per-key ordering on an event timestamp or sequence column, keeping one row per key; for genuinely conflicting rows the right answer may be to reject the batch rather than pick silently. The other half of the fix is the target side: the ON columns should be unique in the target — usually its primary key or a unique key. If they are not, ask whether MERGE is expressing what you actually mean, because "update every target row that loosely matches" is rarely the intent of an upsert. ## Why it is worth knowing This error is the single most common MERGE failure in ETL work, and it usually surfaces in production rather than in testing, because test fixtures rarely contain the duplicate that a real upstream system eventually emits. Treat it as a data-quality signal: the statement is telling you the batch contains two versions of the same fact and nobody decided which one is true. Building the deduplication into the source query — and, ideally, monitoring how often it discards rows — is the durable answer, not retrying the merge.