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 can update the matching row and insert only when no row was updated. Keep sku unique and indexed so SQL Server can protect the key range while the transaction runs. Match the parameter types to the table columns:

DECLARE @sku varchar(30) = 'BK-15';
DECLARE @quantity int = 3;

SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRANSACTION;

    UPDATE inventory WITH (UPDLOCK, SERIALIZABLE)
    SET quantity = quantity + @quantity
    WHERE sku = @sku;

    IF @@ROWCOUNT = 0
    BEGIN
        INSERT INTO inventory (sku, quantity)
        VALUES (@sku, @quantity);
    END;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0
        ROLLBACK TRANSACTION;
    THROW;
END CATCH;

UPDLOCK holds update locks until the transaction completes, and SERIALIZABLE protects the searched key range from a concurrent insert. Those locks can reduce concurrency; validate the pattern, index, and workload before using it. SQL Server also supports MERGE for set-based operations. Microsoft cautions that MERGE can have concurrency complexities at scale. See Microsoft’s table hints, transaction isolation levels, MERGE reference, and the detailed 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.

Browse the cross-database SQL comparison guides for other tasks where SQL syntax differs between engines.