Database / SQLite Interview questions
What is an UPSERT in SQLite (INSERT... ON CONFLICT)?
An upsert inserts a new row, or updates an existing one instead if the insert would violate a uniqueness constraint — a single statement covering both the "create" and "update" case, avoiding a separate check-then-insert-or-update sequence in application code.
INSERT INTO person (id, name, visit_count) VALUES (1, 'Ada', 1) ON CONFLICT(id) DO UPDATE SET visit_count = visit_count + 1;
ON CONFLICT(column) DO UPDATE SET ... specifies exactly what to do when a conflicting row
already exists, and can reference the conflicting existing row's values via excluded.column for
the values that were about to be inserted. There's also a simpler DO NOTHING variant when you
just want to silently skip the insert on conflict rather than updating anything.
What problem does INSERT ... ON CONFLICT DO UPDATE solve?
What does ON CONFLICT DO NOTHING do?
More Related questions...
