SQL UPSERT Syntax by Database

UPSERT is a general name for inserting a row when its key is new and updating the existing row when that key already exists. The SQL syntax differs by database, but the operation normally depends on a PRIMARY KEY or UNIQUE constraint. The examples below assume sku is a unique key in an inventory table.

Syntax at a glance

Database	Insert-or-update syntax	Conflict behavior
MySQL	`INSERT ... ON DUPLICATE KEY UPDATE`	Any primary or unique-key conflict can select an existing row.
MariaDB	`INSERT ... ON DUPLICATE KEY UPDATE`	Similar syntax; `VALUES(column)` refers to the proposed insert value.
PostgreSQL	`INSERT ... ON CONFLICT ... DO UPDATE`	Names the unique conflict target and exposes the proposed row as `EXCLUDED`.
SQLite	`INSERT ... ON CONFLICT ... DO UPDATE`	Uses a unique conflict target and the `excluded` row.
Oracle	`MERGE`	Joins a source row or set to the target, then updates matched rows or inserts unmatched rows.
SQL Server	Transactional `UPDATE` + `INSERT`, or `MERGE`	Use a unique key; account for locking and concurrency behavior.

MySQL and MariaDB

MySQL 8.0.19 and later lets you alias the proposed row after VALUES:

INSERT INTO inventory (sku, quantity)
VALUES ('BK-15', 3) AS incoming
ON DUPLICATE KEY UPDATE
  quantity = inventory.quantity + incoming.quantity;

MariaDB documents VALUES(column) inside the update clause to read the proposed value:

INSERT INTO inventory (sku, quantity)
VALUES ('BK-15', 3)
ON DUPLICATE KEY UPDATE
  quantity = inventory.quantity + VALUES(quantity);

The two syntaxes are similar, but do not copy version-specific MySQL row-alias syntax into a MariaDB query without checking your server version. MySQL deprecated VALUES(column) for this clause in MySQL 8.0.20; MariaDB continues to document it. See the MySQL reference, MariaDB reference, and the detailed MySQL UPSERT and MariaDB UPSERT guides.

Both products trigger this action for primary-key or unique-index conflicts. If multiple unique indexes can conflict with different rows, the result can be surprising; design a clear conflict key. See MariaDB’s duplicate-key behavior and MySQL’s multiple-unique-key caveat.

PostgreSQL and SQLite

PostgreSQL and SQLite use ON CONFLICT and provide the proposed row through EXCLUDED or excluded:

-- PostgreSQL
INSERT INTO inventory (sku, quantity)
VALUES ('BK-15', 3)
ON CONFLICT (sku) DO UPDATE
SET quantity = inventory.quantity + EXCLUDED.quantity;

-- SQLite 3.24.0 and later
INSERT INTO inventory (sku, quantity)
VALUES ('BK-15', 3)
ON CONFLICT (sku) DO UPDATE
SET quantity = inventory.quantity + excluded.quantity;

PostgreSQL’s conflict target identifies which unique constraint or index drives the update. SQLite added UPSERT syntax in version 3.24.0. See the PostgreSQL INSERT reference, SQLite UPSERT reference, and the detailed PostgreSQL and SQLite guides.

Oracle

Oracle uses MERGE to update a matched row or insert a row when no match exists:

MERGE INTO inventory target
USING (
  SELECT :sku AS sku, :quantity AS quantity
  FROM dual
) source
ON (target.sku = source.sku)
WHEN MATCHED THEN
  UPDATE SET target.quantity = target.quantity + source.quantity
WHEN NOT MATCHED THEN
  INSERT (sku, quantity)
  VALUES (source.sku, source.quantity);

The ON condition defines which target row matches the source; use the key columns for that comparison. For batches, replace the DUAL source with a query over the incoming rows. See Oracle’s MERGE reference and the full Oracle UPSERT guide.

SQL Server

For a single application key, a transaction that runs UPDATE and conditionally INSERT is one common pattern. SQL Server also supports MERGE for set-based operations. Microsoft cautions that MERGE can have concurrency complexities at scale; validate it with your indexes and workload. See the Microsoft MERGE reference and the SQL Server UPSERT guide.

Choosing a pattern

Add a unique or primary-key constraint for the key that defines a duplicate.
Use the target syntax for the database where the statement will run; these forms are not fully portable between engines.
For MySQL and MariaDB tables with multiple unique indexes, ensure the insert cannot ambiguously conflict with different existing rows.
For SQL Server, test the selected locking strategy under expected concurrency. MERGE and a transaction-based two-statement pattern have different locking behavior.
REPLACE is not an in-place update in MariaDB: it deletes the conflicting row and inserts another. See the MariaDB REPLACE reference.