Upserts: insert or update

The problem

A warehouse receives a shipment: 10 bags of basmati rice and 24 bottles of mustard oil. Rice is already in the inventory table; mustard oil is new. A plain INSERT of rice fails with "UNIQUE constraint failed: inventory.sku", and a plain UPDATE of oil changes nothing because the row does not exist. You could first SELECT, then decide in app code, but between your SELECT and your write another request may insert the same row. An upsert does the check and the write in one atomic statement.

ON CONFLICT ... DO UPDATE (SQLite, PostgreSQL)

INSERT INTO inventory (sku, name, qty, updated)
VALUES ('RICE5', 'Basmati Rice 5kg', 10, '2025-04-05'),
       ('OIL1',  'Mustard Oil 1L',   24, '2025-04-05')
ON CONFLICT (sku) DO UPDATE
SET qty     = inventory.qty + excluded.qty,
    updated = excluded.updated;
-- DAL1   | Toor Dal 1kg     | 75 | 2025-04-01
-- OIL1   | Mustard Oil 1L   | 24 | 2025-04-05   (inserted)
-- RICE5  | Basmati Rice 5kg | 50 | 2025-04-05   (40 + 10, updated)
-- TIFFIN | Steel Tiffin Box | 12 | 2025-04-01

• ON CONFLICT (sku) names the column(s) of a PRIMARY KEY or UNIQUE constraint. Without such a constraint there is no conflict to catch.
• excluded.qty means "the qty from the VALUES I tried to insert". Writing qty = excluded.qty replaces the old number; qty = inventory.qty + excluded.qty adds to it.
• ON CONFLICT DO NOTHING skips the duplicate row silently.
• SQLite has supported this since version 3.24 and PostgreSQL since 9.5.

MySQL: ON DUPLICATE KEY UPDATE

-- MySQL 8.0.19 and later
INSERT INTO inventory (sku, name, qty) VALUES ('RICE5', 'Basmati Rice 5kg', 10) AS new
ON DUPLICATE KEY UPDATE qty = inventory.qty + new.qty;

SQL Server: MERGE

-- SQL Server (Oracle and PostgreSQL 15+ also have MERGE)
MERGE INTO inventory AS t
USING (VALUES ('RICE5', 'Basmati Rice 5kg', 10)) AS s (sku, name, qty)
ON t.sku = s.sku
WHEN MATCHED THEN UPDATE SET t.qty = t.qty + s.qty
WHEN NOT MATCHED THEN INSERT (sku, name, qty) VALUES (s.sku, s.name, s.qty);

INSERT OR IGNORE and REPLACE (SQLite)

SQLite also has shortcut forms. INSERT OR IGNORE skips any row that would break a UNIQUE, PRIMARY KEY, NOT NULL or CHECK rule and carries on with the rest; it is handy for importing a list that may contain people you already have. MySQL's INSERT IGNORE is similar. Because it hides every such error, check that you only expect duplicates.

INSERT OR IGNORE INTO subscribers (email, name, source)
VALUES ('asha@example.com', 'Asha V', 'csv'),       -- already there: skipped
       ('meena@example.com', 'Meena Joshi', 'csv');  -- new: inserted

REPLACE INTO (or INSERT OR REPLACE) looks like an upsert but is not. On a conflict it DELETES the old row and INSERTS the new one. Every column you did not give is set to its default or NULL, the old values are lost, delete triggers fire, and with ON DELETE CASCADE foreign keys the child rows of the old row can be deleted too. MySQL's REPLACE behaves the same way.

REPLACE INTO inventory (sku, name, qty) VALUES ('DAL1', 'Toor Dal 1kg', 80);
-- DAL1 | Toor Dal 1kg | 80 | NULL     <- updated was '2025-04-01', now lost

Common mistakes

• Using ON CONFLICT on a column with no UNIQUE or PRIMARY KEY constraint. Add the constraint first.
• Writing qty = excluded.qty when you meant to add. Decide: replace (excluded.qty) or add (table.qty + excluded.qty).
• Using REPLACE as an upsert and silently wiping other columns. Use ON CONFLICT DO UPDATE.
• Using INSERT OR IGNORE and hiding real errors such as a NULL in a NOT NULL column. Check the row count afterwards.
• Doing SELECT-then-INSERT in app code. Two requests can both see "not there" and both insert. Let the database decide with an upsert.