⚡ AdvancedintermediateCommonly used

How to upsert in SQL (INSERT or UPDATE)

Insert a row if it does not exist, or update it if it does.

UPSERT (PostgreSQL)PostgreSQL

INSERT INTO users (email, name, country)
VALUES ('alice@example.com', 'Alice', 'Kenya')
ON CONFLICT (email)
DO UPDATE SET
  name    = EXCLUDED.name,
  country = EXCLUDED.country;

If email already exists, update name and country. EXCLUDED refers to the values that were attempted to be inserted.

When to use: Syncing data, updating if exists or inserting if new.

Insert and ignore conflicts (PostgreSQL)PostgreSQL

INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email)
DO NOTHING;

Silently skips the insert if the email already exists.

When to use: When you only want to insert new records, not update existing ones.

UPSERT (MySQL)MySQL

INSERT INTO users (email, name, country)
VALUES ('alice@example.com', 'Alice', 'Kenya')
ON DUPLICATE KEY UPDATE
  name    = VALUES(name),
  country = VALUES(country);

MySQL syntax for upsert. ON DUPLICATE KEY UPDATE runs when a unique/primary key conflict occurs.

MERGE (SQL Server / Oracle)SQL Server

MERGE INTO users AS target
USING (VALUES ('alice@example.com', 'Alice', 'Kenya'))
  AS source (email, name, country)
ON target.email = source.email
WHEN MATCHED THEN
  UPDATE SET name = source.name, country = source.country
WHEN NOT MATCHED THEN
  INSERT (email, name, country)
  VALUES (source.email, source.name, source.country);

The SQL standard MERGE statement. More verbose but very explicit.

Common Mistakes

→PostgreSQL ON CONFLICT requires a specific constraint target (column or constraint name)
→MySQL ON DUPLICATE KEY can fire on either primary key or unique index conflicts

Common Mistakes

Related Topics