examples · mysql · 3 / 4

3. Upsert with ON DUPLICATE KEY UPDATE

shared-credential listeners only

The writes on this page run only against a listener on the shared credential model. A per-user MySQL session is read-only and refuses every write, so if your tenant has any database RBAC grant or any row-level security policy - the condition that forces per-user login - none of this will run against it. Check which model you are on under credential models before you build on this, and write through the HTTP API or the PostgreSQL wire if you are not.

what this does

Inserts a row, or updates the named columns if a row with that key already exists. Write it in the MySQL spelling you already know; the translator rewrites it into the engine's upsert.

what you write

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);

what the engine runs

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.

how it works

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 refused

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 new one, so any column you did not mention comes back as its default. The engine's upsert keeps existing values. Mapping one to the other would look like it worked and quietly discard data, so it is refused instead. INSERT IGNORE is refused for the same reason: swallowing errors is not a behaviour we will emulate.

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.