MERGE Concept

Matching decides which transition applies

MERGE combines supported actions according to how source rows correspond to target rows. A campus inventory feed can contain an item already stored in the target and another item that is new. The first may need an update, while the second may need an insert. The match condition defines that classification, and the supported action clauses define the resulting changes.

The concept is broader than a blanket instruction to copy everything. A matched source might require no change under a particular condition, and some dialects support additional actions. Begin with the business identity and accepted action for each case. A statement can be syntactically valid while matching on the wrong role and modifying the wrong population.

The source needs a defined identity and scope

For the exercises, itemid identifies one inventory item and incoming contains at most one proposed row per itemid. This removes ambiguity about several source rows trying to supply different quantities for the same target. A repeated display label would not be a suitable substitute for identity unless the domain explicitly made it unique.

Real feeds can include retries, duplicate proposals or conflicting versions. They need an explicit precedence or rejection policy before synchronization. Do not let an arbitrary source ordering choose which value wins and then describe the result as a deterministic MERGE. Source quality and identity rules remain part of the contract even when the database offers a convenient combined statement.

Native MERGE syntax belongs to the actual product

SQL Server, Oracle and modern PostgreSQL provide their own native MERGE features and supported clauses. Their exact options, action ordering, return facilities and concurrency behaviour differ. A tutorial should not assume every product implements all variants merely because it recognizes the keyword. PostgreSQL's support begins in a particular version, with later versions extending features.

MySQL 8.4 does not provide that native MERGE statement. Its INSERT ON DUPLICATE KEY UPDATE supports a narrower insert-or-update pattern based on protected key conflicts. That is useful related functionality, but it is not a textual substitute for every MERGE condition or deletion clause. The executable medium task clearly uses MySQL's native upsert form rather than claiming to execute standard MERGE.

The match predicate is not merely a filter decoration

A native MERGE ON condition establishes source-to-target correspondence. Including an unrelated target-state filter there can change whether a source row is classified as matched, not merely restrict a subsequent update. That distinction can create an unexpected insert attempt or affect clauses for missing source matches. Read matching and action predicates as separate semantic decisions.

The simple classification exercise matches item_id only and reports insert or update intent for each incoming identity. It does not mutate data. Existing target identities absent from the incoming source do not appear in that report. This makes the incoming scope explicit before introducing the write operation and avoids accidentally treating every missing feed row as authorization to remove accepted data.

Matched does not automatically mean update everything

A source might contain only some properties or a version that is older than the accepted target. Updating every field blindly can erase meaningful information. State which supplied properties are authoritative for this operation. In the medium exercise, incoming.quantity replaces the matching current quantity exactly; it is not an increment and does not change any other identity.

That distinction matters on retries. Applying replacement quantity 7 again can preserve the same accepted value under the stated contract, while adding 7 again changes it. Neither policy is universally correct. A transaction's wider side effects and conflict reporting still need review, so a stable replacement value alone does not prove an entire application workflow executes exactly once.

Missing-source actions require a complete source contract

Some dialects support an applicable WHEN NOT MATCHED BY SOURCE clause, which can drive actions on target rows without corresponding source rows. A deletion action there is meaningful only when the source represents the intended authoritative scope. A partial feed or incremental batch must not be mistaken for a complete inventory snapshot.

For example, a feed containing changed items 1 and 3 says nothing about whether item 2 should still exist unless the task explicitly defines that absence as removal. Both exercises preserve unrelated target rows. The MySQL upsert has no automatic delete-missing-source step, and the notes do not claim that an insert-or-update operation alone performs full three-way synchronization.

A combined statement does not erase concurrency design

A native combined operation can reduce application coordination between separate commands, but it is not a universal proof that every race or uniqueness conflict disappears. Matching, locking, isolation, indexes and the exact product's semantics still matter under concurrent writers. A slogan that MERGE is always safer than every explicit transaction is too broad.

A separate update-and-insert workflow can also be correct when designed under supported locking and conflict rules. Choose the established implementation that preserves the intended invariant, then verify its actual concurrency behaviour. The isolated exercises do not benchmark competing designs or simulate every concurrent arrival; they establish the stated transformation over supplied synthetic input.

MySQL upsert follows protected key conflicts

INSERT ON DUPLICATE KEY UPDATE takes its update branch when the proposed write encounters the relevant protected key conflict under MySQL's rules. It is not an arbitrary ON join predicate matching any descriptive condition. Multiple unique rules can introduce additional conflict considerations, so a simple primary-key example should not be generalized to every table with several independent uniqueness constraints.

The medium fixture has one explicit primary item identity and compatible input. Its reference uses the selected source quantity directly in the update clause, avoiding a need to teach deprecated VALUES(column) references as the only form. Exact provider support still needs verification on the target; recorded compatible-engine syntax is evidence for the local case, not a substitute for that check.

Integrity still constrains both action paths

An insert path must satisfy the target's required values and checks. An update path must leave a valid accepted row too. A combined syntax is not permission to ignore a missing quantity or negative value when the definition rejects it. The database's actual constraints remain the trusted integrity boundary rather than the source classification label alone.

The lab includes a nonnegative quantity check and strict required-value rules. Positive incoming rows satisfy that domain, while selected invalid later writes are rejected. A valid source-to-target match does not make an otherwise invalid payload acceptable, and a successful classification query does not authorize applying the proposed write to application inventory.

Explain preserved target state explicitly

A full post-state read should show both touched and untouched target rows. If only the updated item is inspected, a mistaken implementation that removed unrelated items could appear correct. The reference expected output includes item 2, which is absent from the incoming batch and must remain at its original quantity.

Empty incoming input provides another useful boundary. It should leave the target unchanged rather than interpret absence as an empty authoritative snapshot. An initially empty target with supplied incoming rows should take only insertion paths. These cases distinguish a narrow upsert contract from an accidental destructive synchronization policy.

Source multiplicity can make native actions invalid

Some native MERGE forms reject ambiguous repeated source matches or have restrictions on changing the same target more than allowed. The precise outcome is dialect specific. That is a reason to define source cardinality before writing SQL, not to assume a single statement chooses the desired result from contradictory proposals automatically.

The exercises enforce incoming item_id as a primary key so one proposed quantity corresponds to each identity. A larger workflow with versioned events might instead aggregate or select the accepted latest event under an explicit order. Such a transformation needs its own ties, missing-version and retry cases. Do not introduce silent deduplication without a domain rule for which fact survives.

Validate the narrower operation you actually implemented

The introductory task reports update for an incoming identity already present and insert for a new identity. The medium task applies the stated replacement quantities through MySQL upsert and reads the full target in id order. Cases cover empty incoming input, empty target and preservation of unrelated existing rows. These are separate meaningful observations of the declared scope.

Recorded MariaDB compatibility execution verifies those query and upsert fixtures, not native PostgreSQL, Oracle or SQL Server MERGE, exact MySQL/provider execution or production concurrency. The prepared content migration stores the learning package without applying its inventory changes to application data. Complete explanatory content can teach the shared matching idea while keeping every product's actual syntax and unverified guarantees distinct.

References

• MySQL 8.4 INSERT ON DUPLICATE KEY UPDATE
• PostgreSQL 18 MERGE