Upsert: insert a row, or update the one already there

SQLite and PostgreSQL

Both use ON CONFLICT with the same basic syntax, so a query written for one runs on the other.

-- Insert, or update the row that is already there.
INSERT INTO wishlists (customer_id, product_id, added_at)
VALUES (1, 288, '2026-01-01 00:00:00')
ON CONFLICT (customer_id, product_id)
DO UPDATE SET added_at = excluded.added_at;

Specify the columns that determine a conflict. excluded refers to the row that was about to be inserted. Run the statement twice: the first run inserts, and the second updates.

SELECT customer_id, product_id, added_at
FROM wishlists
WHERE customer_id = 1 AND product_id = 288;

The other option is to leave the existing row unchanged:

-- "Insert if it is not there, otherwise leave it alone." No error.
INSERT INTO wishlists (customer_id, product_id, added_at)
VALUES (1, 288, '1999-12-31 00:00:00')
ON CONFLICT (customer_id, product_id) DO NOTHING;

Nothing changes and no error is raised. A plain INSERT would instead fail on the primary key.

INSERT INTO wishlists (customer_id, product_id, added_at)
VALUES (1, 288, '2026-01-01 00:00:00')
ON CONFLICT (customer_id, product_id)
DO UPDATE SET added_at = EXCLUDED.added_at;

-- DO NOTHING is the same idea as SQLite's.
INSERT INTO wishlists (customer_id, product_id)
VALUES (1, 288)
ON CONFLICT DO NOTHING;

PostgreSQL spells excluded in upper case by convention, and accepts either. It also allows WHERE on the update branch, so you can update only when some condition holds.

MySQL and MariaDB

-- MySQL 8.0.20 and later: alias the new row instead of VALUES().
INSERT INTO wishlists (customer_id, product_id, added_at)
VALUES (1, 288, '2026-01-01 00:00:00') AS new
ON DUPLICATE KEY UPDATE added_at = new.added_at;

-- Older MySQL and MariaDB:
INSERT INTO wishlists (customer_id, product_id, added_at)
VALUES (1, 288, '2026-01-01 00:00:00')
ON DUPLICATE KEY UPDATE added_at = VALUES(added_at);

This clause differs in behavior as well as name. It reacts to a violation of any unique constraint, not one you specify. On a table with several unique keys, an unexpected key can trigger the update. MySQL 8.0.20 deprecated VALUES(col) in favor of aliasing the new row, so new MySQL code should use AS new; MariaDB still uses VALUES().

SQL Server and Oracle

MERGE INTO wishlists AS target
USING (SELECT 1 AS customer_id, 288 AS product_id, '2026-01-01' AS added_at) AS source
    ON target.customer_id = source.customer_id
   AND target.product_id  = source.product_id
WHEN MATCHED THEN
  UPDATE SET added_at = source.added_at
WHEN NOT MATCHED THEN
  INSERT (customer_id, product_id, added_at)
  VALUES (source.customer_id, source.product_id, source.added_at);

MERGE is the SQL-standard form and is more verbose: write the incoming row as a query, join it to the target, and define each action. WHEN NOT MATCHED BY SOURCE THEN DELETE can also perform a full synchronization. Oracle does not need a trailing semicolon inside PL/SQL, while SQL Server requires one.

For a single row on SQL Server, many teams do an UPDATE followed by an INSERT guarded by @@ROWCOUNT inside a transaction instead, because MERGE has a long list of reported concurrency and trigger bugs. If you use MERGE there, hold it under a transaction with the right isolation, and do not treat it as atomic by itself.

REPLACE is not an upsert

SQLite and MySQL both provide REPLACE, but it is not an UPSERT shortcut.

-- REPLACE deletes the conflicting row and inserts a new one, so
-- added_at is not kept — it falls back to its default. Run the check
-- above again afterwards and compare.
INSERT OR REPLACE INTO wishlists (customer_id, product_id)
VALUES (1, 288);

Run the check query again: added_at now contains the current time instead of the 2026 date. REPLACE deletes the conflicting row and inserts a new row, so columns you did not list revert to their defaults. It can also fire delete triggers and cascade to child rows through foreign keys.

Use REPLACE only when you intend to discard the old row. For an insert-or-update operation, use ON CONFLICT.