SQL Querying

MERGE, Upsert, and RETURNING

Upsert resolves a uniqueness conflict by inserting, updating, or doing nothing; MERGE chooses actions from a source-to-target match; returning features expose changed rows or generated values. These facilities overlap but are not semantically interchangeable across databases.

The conflict or match key is a business rule and should be backed by a primary key or unique constraint.
Upsert is atomic conflict handling, not “select then write.”
MERGE is source-target synchronization.
PostgreSQL and SQLite use ON CONFLICT; MySQL uses ON DUPLICATE KEY UPDATE; SQL Server and Oracle provide MERGE.
Generated-key retrieval is connection and statement sensitive.
Returned rows describe statement effects, not necessarily every trigger, cascade, or later transaction outcome; commit still determines durability.

One intent, five non-equivalent forms

Dialect feature comparison
Database	Conflict/sync form	Changed-row output	Important caveat
PostgreSQL	`INSERT ... ON CONFLICT` or `MERGE`	`RETURNING`	Conflict target can name/infer a unique arbiter
MySQL	`INSERT ... ON DUPLICATE KEY UPDATE`	Connection API / `LAST_INSERT_ID()`	Any duplicate unique key may trigger; avoid ambiguous multiple unique keys
SQLite	`INSERT ... ON CONFLICT`	`RETURNING`	Upsert reacts to uniqueness constraints; returning order is arbitrary
SQL Server	`MERGE` or separate DML	`OUTPUT inserted...`	Match condition and concurrency plan require careful testing
Oracle	`MERGE`	`RETURNING ... INTO` for DML	MERGE is join-based; returning binds differ from a result set

Upsert product by SKU

INSERT INTO products (sku, name, current_price)
VALUES ($1, $2, $3)
ON CONFLICT (sku) DO UPDATE
SET name = EXCLUDED.name,
    current_price = EXCLUDED.current_price
RETURNING product_id, sku, current_price;

These statements are not portable equivalents: conflict selection, aliases, concurrency, and output differ. PostgreSQL appears first.

Retrieve a generated order key

INSERT INTO orders (customer_id, placed_at, status)
VALUES ($1, CURRENT_TIMESTAMP, 'PENDING')
RETURNING order_id;

Statement output is preferable to a later `MAX(id)` query, which races with other sessions.

A unique constraint on products.sku decides whether the incoming SKU is new. PostgreSQL and SQLite name that conflict target; MySQL may react to any violated unique key; SQL Server and Oracle match a source relation to the target. The latter forms therefore demand a source with at most one row per SKU and explicit concurrency testing.