Insert or update a primary key match

← MySQL examples

Connection requirementsCopy Anchor Link

This recipe needs an existing listener using shared credentials. A per-user MySQL session is read-only. Database RBAC grants or row-level policies require that per-user model, so the writes below do not apply there.

Check credential requirements. Use the HTTP API for authorized writes when this listener is read-only. Current PostgreSQL database logins are also read-only.

ON DUPLICATE KEY UPDATE inserts a row or updates its existing primary-key match on the described listener.

Before you run it: Ambiguous secondary-unique-index cases are refused. REPLACE INTO and INSERT IGNORE have different meanings and are not substitutes.

Use the same orders table with id as its primary key. The first statement writes o_1. Running it again with a different total_cents or status demonstrates an update; running it twice unchanged only repeats the same values.

Follow the exampleCopy Anchor Link

1. Confirm shared-credential write access.
2. Create an unambiguous primary key.
3. Run the upsert twice.
4. Read back the changed fields.

Run the upsertCopy Anchor Link

INSERT INTO orders (id, customer, total_cents, status)
VALUES ('o_1', 'c_7', 4200, 'open')
ON DUPLICATE KEY UPDATE
  total_cents = VALUES(total_cents),
  status      = VALUES(status);

How the upsert is translatedCopy Anchor Link

INSERT INTO orders (id, customer, total_cents, status)
VALUES ('o_1', 'c_7', 4200, 'open')
ON CONFLICT (id) DO UPDATE SET
  total_cents = EXCLUDED.total_cents,
  status      = EXCLUDED.status;

VALUES(col) becomes EXCLUDED.col - the value the statement tried to insert.

Understand the behaviorCopy Anchor Link

• MySQL's syntax leaves the conflict target implicit - "duplicate key" does not say which key. The engine's upsert needs it named, so the translator resolves it from the catalog and writes it in.
• When that resolution is ambiguous it stops instead of guessing. A table carrying a secondary unique index is refused, because picking the wrong arbiter would silently update on the wrong key - a wrong answer rather than an error. On such a table, write the upsert as an explicit INSERT … ON CONFLICT through the SQL endpoint, where you name the target yourself.

Why REPLACE INTO is differentCopy Anchor Link

mysql> REPLACE INTO orders (id, status) VALUES ('o_1', 'open');
ERROR 1235 (42000): REPLACE INTO is not supported — its delete-then-insert
semantics reset unmentioned columns to their defaults, which is not what the
engine's upsert does. Use INSERT … ON DUPLICATE KEY UPDATE.

REPLACE INTO deletes the old row and inserts a replacement; omitted fields can therefore change. The engine upsert preserves existing values, so REPLACE INTO is refused rather than treated as an upsert. INSERT IGNORE is also refused because it suppresses errors.

MySQL is a registered trademark of Oracle Corporation and/or its affiliates. OriginChainDB is not affiliated with, endorsed by, or sponsored by Oracle Corporation. OriginChainDB implements a compatible wire protocol so that existing MySQL clients can connect to it; it does not distribute MySQL software.

Expected resultCopy Anchor Link

The supported form is translated to an engine upsert.

ContinueCopy Anchor Link

Continue to read table metadata.