How to insert or update if a row exists in SQL (upsert)

1INSERT … ON CONFLICT DO UPDATERecommended

Write the insert as usual, then name the key that may collide in ON CONFLICT (sku) and say what to change in DO UPDATE SET. Inside the SET, the special table excluded is the row that failed to insert, so price = excluded.price copies the new price over the old one. Each row of a multi-row VALUES is decided on its own: some can update while others insert.

CREATE TABLE products (
    sku   TEXT PRIMARY KEY,
    name  TEXT NOT NULL,
    price INTEGER NOT NULL,
    stock INTEGER NOT NULL DEFAULT 0
);
INSERT INTO products VALUES ('A1', 'Mug', 8, 12), ('B2', 'Cap', 15, 4);

-- A1 exists, so it is updated; C3 doesn't, so it is inserted
INSERT INTO products (sku, name, price)
VALUES ('A1', 'Big mug', 9), ('C3', 'Tote', 12)
ON CONFLICT (sku) DO UPDATE SET
    name  = excluded.name,
    price = excluded.price;

SELECT * FROM products ORDER BY sku;

A1 keeps its row and its stock of 12, but now reads Big mug at 9; C3 is new, with the default stock of 0; B2 wasn't in the insert and is untouched. Only the columns listed in SET change on an update, so leave out any column you want to keep, such as a created-at date. This syntax needs SQLite 3.24 or later (2018) and PostgreSQL 9.5 or later. In SQLite, an upsert fed by INSERT … SELECT needs a WHERE true after the SELECT; without it the parser reads ON as part of a join and fails with near "DO": syntax error.

2A counter: insert 1, or add 1

The SET can compute the new value from the old one. That turns an upsert into a counter: the first event for a key inserts a row with 1, and every later event adds 1 to it. Qualify the old value with the table name, page_views.views; SQLite accepts a bare views, but PostgreSQL rejects it as ambiguous. Add RETURNING (SQLite 3.35+) to get the row back after the insert or update.

CREATE TABLE page_views (
    page  TEXT PRIMARY KEY,
    views INTEGER NOT NULL
);

-- The first visit inserts 1; every later visit adds 1
INSERT INTO page_views VALUES ('/home', 1)
ON CONFLICT (page) DO UPDATE SET views = page_views.views + 1;
INSERT INTO page_views VALUES ('/home', 1)
ON CONFLICT (page) DO UPDATE SET views = page_views.views + 1;
INSERT INTO page_views VALUES ('/docs', 1)
ON CONFLICT (page) DO UPDATE SET views = page_views.views + 1;

INSERT INTO page_views VALUES ('/home', 1)
ON CONFLICT (page) DO UPDATE SET views = page_views.views + 1
RETURNING page, views;

SELECT * FROM page_views ORDER BY page;

Three visits to /home leave it at 3 and one visit to /docs leaves it at 1. RETURNING prints /home with 3, the value after the update, so you can read the new count without a second query. Because the whole thing is one statement, two sessions counting at once can't both insert the first row; one of them takes the update path. Adding excluded.views instead of 1 turns the same pattern into a running total.

3Insert only if the row doesn't exist: DO NOTHING

When an existing row should win, replace DO UPDATE … with DO NOTHING. A clash on the key skips that row without an error, and the rest of the statement carries on. changes() shows how many rows the last statement inserted. Leaving out the target, as in plain ON CONFLICT DO NOTHING, skips a clash on any primary key or unique column.

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    name  TEXT NOT NULL
);
INSERT INTO users (email, name) VALUES ('[email protected]', 'Ana');

-- Insert only if the email isn't taken; otherwise skip quietly
INSERT INTO users (email, name) VALUES ('[email protected]', 'Ana B')
ON CONFLICT (email) DO NOTHING;
SELECT changes() AS inserted;

-- With no target, DO NOTHING skips a conflict on any unique column
INSERT INTO users (email, name) VALUES ('[email protected]', 'Ben')
ON CONFLICT DO NOTHING;

-- SQLite's older spelling of the same thing
INSERT OR IGNORE INTO users (email, name) VALUES ('[email protected]', 'Benny');

SELECT * FROM users;

The second insert for [email protected] inserts 0 rows, so Ana keeps her name. Ben is new and goes in with id 2, and the INSERT OR IGNORE that follows changes nothing because his email is now taken. INSERT OR IGNORE is SQLite-only and also ignores NOT NULL and CHECK failures, so prefer ON CONFLICT DO NOTHING, which skips only key clashes and also runs on PostgreSQL.

4The conflict target must be a PRIMARY KEY or UNIQUE constraint

ON CONFLICT only fires when an insert breaks a uniqueness rule, so the columns in the parentheses must match a PRIMARY KEY, a UNIQUE constraint or a unique index exactly. For a key made of several columns, list all of them. Naming a column with no such constraint isn't a silent no-op: the statement fails, as the last insert shows.

CREATE TABLE stock (
    shop  TEXT NOT NULL,
    item  TEXT NOT NULL,
    qty   INTEGER NOT NULL,
    note  TEXT,
    UNIQUE (shop, item)
);
INSERT INTO stock VALUES ('north', 'pen', 5, NULL);

-- The target names the columns of a UNIQUE constraint: (shop, item)
INSERT INTO stock VALUES ('north', 'pen', 3, NULL), ('south', 'pen', 7, NULL)
ON CONFLICT (shop, item) DO UPDATE SET qty = stock.qty + excluded.qty;
SELECT shop, item, qty FROM stock ORDER BY shop;

-- note has no UNIQUE constraint, so it can't be a conflict target
INSERT INTO stock VALUES ('north', 'ink', 1, 'x')
ON CONFLICT (note) DO NOTHING;

The (shop, item) pair adds 3 to the 5 pens at north, giving 8, and inserts the 7 pens at south as a new row. The last statement fails with ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint. If your table has no such constraint yet, add one with CREATE UNIQUE INDEX … ON stock (shop, item) before you upsert; without it the database has nothing to detect a duplicate with.

5Why INSERT OR REPLACE is not an upsert

SQLite's INSERT OR REPLACE (also spelled REPLACE INTO) looks like an upsert but works differently: on a clash it deletes the old row and inserts a brand new one. Every column you didn't mention falls back to its default, and an INTEGER PRIMARY KEY gets a new value. The embed replaces A1 one way and updates B2 the other way.

CREATE TABLE products (
    id    INTEGER PRIMARY KEY,
    sku   TEXT NOT NULL UNIQUE,
    price INTEGER NOT NULL,
    stock INTEGER NOT NULL DEFAULT 0
);
INSERT INTO products (sku, price, stock) VALUES ('A1', 8, 12), ('B2', 15, 4);

-- INSERT OR REPLACE deletes the old A1 row and inserts a new one
INSERT OR REPLACE INTO products (sku, price) VALUES ('A1', 9);
SELECT * FROM products ORDER BY id;

-- The upsert keeps the row: same id, stock untouched
INSERT INTO products (sku, price) VALUES ('B2', 16)
ON CONFLICT (sku) DO UPDATE SET price = excluded.price;
SELECT * FROM products ORDER BY id;

After the replace, A1 has moved from id 1 to id 3 and its stock of 12 has dropped to 0, the column default, because the statement didn't mention stock. The upsert on B2 changes only the price to 16 and keeps id 2 and stock 4. The delete is a real delete, so it also fires delete triggers and ON DELETE CASCADE: with PRAGMA foreign_keys = ON, replacing a product this way removes its reviews too. Use INSERT OR REPLACE only when the new row really should replace every column of the old one; for "update if it exists", use ON CONFLICT DO UPDATE.

6The portable two-step: INSERT … SELECT … WHERE NOT EXISTS

Every database can insert a row only when no match exists, by inserting from a SELECT that returns one row or none. WHERE NOT EXISTS (…) decides which. For "update or insert", run an UPDATE first and then the same guarded INSERT: if the update found the row, the insert adds nothing. The same NOT EXISTS test is how you find rows with no match in another table.

CREATE TABLE users (
    email TEXT PRIMARY KEY,
    name  TEXT NOT NULL
);
INSERT INTO users VALUES ('[email protected]', 'Ana');

-- Insert if not exists: works in every database
INSERT INTO users (email, name)
SELECT '[email protected]', 'Ben'
WHERE NOT EXISTS (
    SELECT 1 FROM users WHERE email = '[email protected]'
);

-- Update or insert: UPDATE first, then INSERT only if no row matched
UPDATE users SET name = 'Ana B' WHERE email = '[email protected]';
INSERT INTO users (email, name)
SELECT '[email protected]', 'Ana B'
WHERE NOT EXISTS (
    SELECT 1 FROM users WHERE email = '[email protected]'
);

SELECT * FROM users ORDER BY email;

Ben is inserted because no row had his email; Ana's row is updated to Ana B and the guarded insert after it adds nothing. The same two statements ran unchanged on PostgreSQL, MySQL and SQL Server (Babelfish). The catch is concurrency: between the check and the insert, another session can add the same key. Keep a PRIMARY KEY or UNIQUE constraint on the column so the loser of that race gets an error instead of a duplicate row, and use the one-statement upsert wherever your database has one.

7Upsert in PostgreSQL, MySQL and SQL Server

Each database spells the upsert its own way. Every row below was run on PostgreSQL 18, MySQL 8.4 or SQL Server's T-SQL through Babelfish, except where the notes say otherwise. Oracle has no ON CONFLICT; its upsert is MERGE.