An upsert inserts a row, or updates it if a row with the same key already exists — atomically, in one statement, without a race between “check” and “write”.

INSERT … ON CONFLICT (PostgreSQL, SQLite)

SQL

CREATE TABLE inventory (
    sku        TEXT PRIMARY KEY,
    qty        INTEGER NOT NULL,
    updated_on TEXT NOT NULL
);
INSERT INTO inventory VALUES ('PEN', 100, '2024-03-01'), ('DESK', 5, '2024-03-01');

INSERT INTO inventory (sku, qty, updated_on) VALUES
    ('PEN',   50, '2024-03-10'),      -- exists  → add to stock
    ('CHAIR', 12, '2024-03-10')       -- new     → insert
ON CONFLICT (sku) DO UPDATE
    SET qty        = inventory.qty + excluded.qty,
        updated_on = excluded.updated_on;

SELECT * FROM inventory ORDER BY sku;

Output

sku   | qty | updated_on
------+-----+-----------
CHAIR | 12  | 2024-03-10
DESK  | 5   | 2024-03-01
PEN   | 150 | 2024-03-10

excluded is the row that would have been inserted; inventory.qty is the existing row. ON CONFLICT (sku) DO NOTHING simply skips duplicates.

Other dialects

MySQL

INSERT INTO inventory (sku, qty, updated_on) VALUES ('PEN', 50, '2024-03-10')
ON DUPLICATE KEY UPDATE
    qty = qty + VALUES(qty),          -- MySQL 8.0.20+: prefer an alias, e.g. AS new … new.qty
    updated_on = VALUES(updated_on);

SQL Server / Oracle / PostgreSQL 15+ (MERGE)

MERGE INTO inventory AS t
USING (VALUES ('PEN', 50, '2024-03-10')) AS s (sku, qty, updated_on)
    ON t.sku = s.sku
WHEN MATCHED THEN
    UPDATE SET qty = t.qty + s.qty, updated_on = s.updated_on
WHEN NOT MATCHED THEN
    INSERT (sku, qty, updated_on) VALUES (s.sku, s.qty, s.updated_on);

The REPLACE trap

SQLite's INSERT OR REPLACE (and MySQL's REPLACE) deletes the old row and inserts a new one — columns you did not supply fall back to defaults.

SQL

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, plan TEXT DEFAULT 'free');
INSERT INTO users VALUES (1, 'Asha', 'premium');

INSERT OR REPLACE INTO users (id, name) VALUES (1, 'Asha R');   -- plan is lost!
SELECT * FROM users;

Output

id | name   | plan
---+--------+-----
1  | Asha R | free

How do you implement an upsert?