SQL UPSERT – INSERT or UPDATE on Conflict

What Is UPSERT?

Without UPSERT, you would need to check for existence first, then branch to INSERT or UPDATE. This is a race condition waiting to happen in concurrent applications:

SQL

-- The unsafe manual approach (race condition between SELECT and INSERT)
SELECT id FROM user_settings WHERE user_id = 42;
-- If row exists: UPDATE ...
-- If not: INSERT ...

-- UPSERT solves this atomically in one statement

ℹ️

UPSERT is not standard SQL

The SQL standard defines a MERGE statement (covered below) but every major database has its own UPSERT syntax. PostgreSQL, MySQL, and SQLite all use different keywords. Choose the syntax for your target database.

PostgreSQL: ON CONFLICT

PostgreSQL's UPSERT uses ON CONFLICT (unique_column) DO UPDATE SET. The special EXCLUDED pseudo-table holds the values from the attempted insert:

SQL

-- Upsert a user setting: insert if not present, update if exists
INSERT INTO user_settings (user_id, theme, notifications)
VALUES (42, 'dark', true)
ON CONFLICT (user_id)
DO UPDATE SET
    theme         = EXCLUDED.theme,
    notifications = EXCLUDED.notifications,
    updated_at    = NOW();
-- EXCLUDED.theme refers to the 'dark' value from the attempted INSERT

SQL

-- Increment a page view counter (insert on first visit, increment on subsequent)
INSERT INTO page_views (page_slug, view_count)
VALUES ('/home', 1)
ON CONFLICT (page_slug)
DO UPDATE SET
    view_count = page_views.view_count + 1,
    last_viewed = NOW();

-- Do nothing on conflict (idempotent insert)
INSERT INTO events (event_id, event_type, payload)
VALUES ('evt_001', 'signup', '{"email":"a@b.com"}')
ON CONFLICT (event_id)
DO NOTHING;   -- silently skip if event_id already exists

MySQL: ON DUPLICATE KEY UPDATE

MySQL triggers UPSERT when a unique or primary key constraint is violated:

SQL

-- MySQL UPSERT: ON DUPLICATE KEY UPDATE
INSERT INTO user_settings (user_id, theme, notifications)
VALUES (42, 'dark', 1)
ON DUPLICATE KEY UPDATE
    theme         = VALUES(theme),
    notifications = VALUES(notifications),
    updated_at    = NOW();
-- VALUES(col) refers to the value from the INSERT clause

-- MySQL 8.0.20+ alias syntax (VALUES() is deprecated in newer versions):
INSERT INTO user_settings (user_id, theme)
VALUES (42, 'dark') AS new_row
ON DUPLICATE KEY UPDATE
    theme = new_row.theme;

-- Increment counter with MySQL UPSERT
INSERT INTO page_views (page_slug, view_count)
VALUES ('/home', 1)
ON DUPLICATE KEY UPDATE
    view_count  = view_count + 1,
    last_viewed = NOW();

SQLite: INSERT OR REPLACE and ON CONFLICT

SQL

-- SQLite: INSERT OR REPLACE (deletes the old row, inserts a new one)
-- ⚠️ This resets ALL columns and auto-increment ID — use with care
INSERT OR REPLACE INTO user_settings (user_id, theme, notifications)
VALUES (42, 'dark', 1);

-- SQLite 3.24+: supports PostgreSQL-style ON CONFLICT syntax
INSERT INTO user_settings (user_id, theme, notifications)
VALUES (42, 'dark', 1)
ON CONFLICT (user_id)
DO UPDATE SET
    theme         = excluded.theme,
    notifications = excluded.notifications;

-- SQLite: INSERT OR IGNORE (like ON CONFLICT DO NOTHING)
INSERT OR IGNORE INTO events (event_id, payload)
VALUES ('evt_001', '{}');

⚠️

INSERT OR REPLACE vs ON CONFLICT DO UPDATE

REPLACE (MySQL) and INSERT OR REPLACE (SQLite) delete the conflicting row first, then insert a new one. This: (1) changes the row's primary key / auto-increment ID; (2) resets any columns not in the INSERT to their defaults; (3) fires DELETE triggers then INSERT triggers. Use ON CONFLICT DO UPDATE / ON DUPLICATE KEY UPDATE to modify only specific columns in place.

Common Use Cases

Use Case	Pattern
Sync external data	Upsert every row from a CSV/API — inserts new records, updates changed ones.
Page / event counters	Insert with count=1 on first occurrence, increment on subsequent.
Last-seen timestamps	Insert user + last_seen on first activity, update last_seen on subsequent.
Idempotent pipelines	ON CONFLICT DO NOTHING ensures re-running a job does not create duplicates.
Feature flags / settings	Insert default settings for new users, update specific keys for existing users.

SQL

-- Sync product catalogue from an external feed (PostgreSQL)
INSERT INTO products (sku, name, price, stock, updated_at)
SELECT sku, name, price, stock, NOW()
FROM   staging_products           -- temporary staging table
ON CONFLICT (sku)
DO UPDATE SET
    name       = EXCLUDED.name,
    price      = EXCLUDED.price,
    stock      = EXCLUDED.stock,
    updated_at = EXCLUDED.updated_at
WHERE  products.price   != EXCLUDED.price
    OR products.stock   != EXCLUDED.stock
    OR products.name    != EXCLUDED.name;
-- The WHERE clause makes it a no-op if nothing changed (saves write I/O)

Summary

📋 Summary

UPSERT atomically inserts a row if it does not exist, or updates it if it does — no race conditions.
PostgreSQL: INSERT ... ON CONFLICT (col) DO UPDATE SET col = EXCLUDED.col.
MySQL: INSERT ... ON DUPLICATE KEY UPDATE col = VALUES(col).
SQLite: INSERT OR REPLACE (full row replace) or INSERT ... ON CONFLICT DO UPDATE (3.24+).
ON CONFLICT DO NOTHING / INSERT OR IGNORE silently skips duplicates — useful for idempotent inserts.
Use cases: data sync, counters, last-seen timestamps, idempotent pipelines.

FAQ

What is the SQL MERGE statement and how does it differ from UPSERT? +

MERGE (SQL:2003 standard) is a more powerful version of UPSERT. It can INSERT, UPDATE, and DELETE in a single statement based on whether source rows match the target: MERGE INTO target USING source ON condition WHEN MATCHED THEN UPDATE ... WHEN NOT MATCHED THEN INSERT .... Supported by SQL Server, Oracle, and PostgreSQL 15+. MySQL does not support MERGE. For simple insert-or-update, the database-specific UPSERT syntax is simpler and more readable.

Can ON CONFLICT detect conflicts on multiple columns? +

Yes. PostgreSQL allows ON CONFLICT (col1, col2) to match a composite unique constraint. The conflict must be on a column or combination of columns covered by a UNIQUE or PRIMARY KEY constraint. You can also use ON CONFLICT ON CONSTRAINT constraint_name to reference a named constraint directly.

Is UPSERT atomic? Could two concurrent UPSERTs create duplicate rows? +

Yes, UPSERT is atomic in all major databases. The conflict detection and the insert/update operation are a single atomic unit — concurrent UPSERTs on the same unique key will not create duplicates. This is the entire point of UPSERT vs the manual SELECT → INSERT/UPDATE pattern, which has a race condition window between the SELECT and the subsequent write.