UPSERT (SQLite ON CONFLICT)

UPSERT (SQLite ON CONFLICT) is a SQL statement in the SQLite Specific category. SQLite 3.24+: upsert with ON CONFLICT. The excluded keyword refers to the proposed inserted values. The syntax is INSERT INTO table VALUES (...) ON CONFLICT(column) DO UPDATE SET col=excluded.col;. It returns upsert result. A typical example: CREATE TABLE counter (page TEXT PRIMARY KEY, views INT DEFAULT 0); INSERT INTO counter VALUES ('home', 1) ON CONFLICT(page) DO UPDATE SET views = views + 1; -- Atomically: insert if new, increment if exists -- More complex: INSERT INTO users (id, name, email) VALUES (1, 'Alice', 'alice@test.com') ON CONFLICT(id) DO UPDATE SET name = excluded.name, email = excluded.email WHERE users.email IS DISTINCT FROM excluded.email; -- Only update if values… A close relative is AUTOINCREMENT (SQLite), which sQLite: unlike AUTO_INCREMENT in MySQL, only INTEGER PRIMARY KEY columns auto-increment. AUTOINCREMENT guarantees unique increasing IDs. A close relative is ROWID / _ROWID_, which sQLite: every row has an implicit 64-bit ROWID. INTEGER PRIMARY KEY becomes an alias for ROWID.