INSERT, UPDATE, DELETE

The three statements that modify rows — INSERT, UPDATE, and DELETE — together form the Data Manipulation Language of SQL. They are the writing complement to SELECT; the four together exhaust the standard read-write surface of a relational database. This page documents the syntax of each, the conflict-resolution clauses that handle constraint violations, the upsert pattern (ON CONFLICT … DO UPDATE), and the RETURNING clause that returns the affected rows from a modification statement.

INSERT

The simplest form of INSERT:

INSERT INTO customer (name, email) VALUES ('Ada Lovelace', 'ada@example.com');

The table name is followed by an optional column list and a VALUES clause. Columns omitted from the column list receive their declared DEFAULT (or NULL if no default is declared). The values are positional; the nth value is assigned to the nth column in the column list. If the column list is omitted, the values are matched against the table’s columns in declaration order:

INSERT INTO customer VALUES (1, 'Ada Lovelace', 'ada@example.com', '+44 …');

The omitted-column-list form is convenient but fragile: a schema change that adds, reorders, or removes columns invalidates every such statement. The recommendation in production code is to always supply the column list.

Multiple rows

A single INSERT may supply several rows by supplying several VALUES tuples:

INSERT INTO customer (name, email) VALUES
    ('Ada Lovelace',    'ada@example.com'),
    ('Donald Knuth',    'don@example.com'),
    ('Grace Hopper',    NULL);

Multi-row inserts are substantially more efficient than several single-row inserts, both because the engine performs less per-statement bookkeeping and because the index updates are batched. For bulk loading, the multi-row form (or INSERT … SELECT) inside a single transaction is the conventional pattern.

INSERT … SELECT

A query may supply the values:

INSERT INTO customer_archive (id, name, email)
SELECT id, name, email FROM customer WHERE deleted_at IS NOT NULL;

The projection of the SELECT is matched positionally to the column list of the INSERT. The form is the conventional mechanism for archiving, for moving rows between tables in a normalisation, and for materialising query results into persistent storage.

DEFAULT VALUES

A row consisting entirely of column defaults is inserted with the DEFAULT VALUES form:

INSERT INTO event DEFAULT VALUES;

This is useful when the table’s columns all have meaningful defaults (an id autoincrement, a created_at timestamp, a status default of 'pending') and the application does not need to override any of them.

Conflict resolution

A constraint violation during INSERT triggers the table’s conflict resolution policy. Five strategies are accepted, declared either on the constraint, on the table, or on the statement:

Strategy	On violation
ABORT	(default) The statement aborts; prior changes in the transaction remain.
ROLLBACK	The entire transaction rolls back.
FAIL	The statement aborts; changes already made by the statement remain.
IGNORE	The offending row is silently skipped.
REPLACE	The pre-existing conflicting row is deleted; the new row replaces it.

The strategy is most often specified on the statement:

INSERT OR IGNORE INTO customer (email) VALUES ('ada@example.com');
INSERT OR REPLACE INTO customer (email) VALUES ('ada@example.com');

INSERT OR IGNORE is the conventional way to insert rows that may already exist without an error; INSERT OR REPLACE is the conventional way to overwrite a previous row. Both predate the proper UPSERT mechanism added in 3.24.

UPSERT

The ON CONFLICT … DO UPDATE clause, added in version 3.24 (2018), is the proper UPSERT: an INSERT that becomes an UPDATE when a uniqueness conflict occurs.

INSERT INTO customer (id, name, email)
VALUES (1, 'Ada Lovelace', 'ada@example.com')
ON CONFLICT (id) DO UPDATE SET
    name  = excluded.name,
    email = excluded.email;

The conflict target — (id) here — is a column or set of columns with a unique index (a primary key or a UNIQUE constraint). When the insert would violate the uniqueness, the UPDATE is run instead. The pseudo-table excluded refers to the values that would have been inserted; the row’s existing values are accessible directly by column name.

The ON CONFLICT clause may include a WHERE predicate, which restricts the update to a subset of conflicting rows:

INSERT INTO inventory (sku, quantity)
VALUES ('A1', 5)
ON CONFLICT (sku) DO UPDATE SET quantity = quantity + excluded.quantity
WHERE quantity < 1000;

The clause DO NOTHING is a synonym for the older INSERT OR IGNORE and is the form preferred in modern SQLite:

INSERT INTO log (message) VALUES (?) ON CONFLICT DO NOTHING;

The proper UPSERT — ON CONFLICT … DO UPDATE — is preferred over INSERT OR REPLACE because the older form deletes the conflicting row before inserting the new one, which fires DELETE triggers and cascades referential actions. The proper form updates in place, preserving foreign-key relationships and audit history.

UPDATE

The UPDATE statement modifies rows that already exist:

UPDATE customer
SET email = 'ada@new.example.com'
WHERE id = 1;

The SET clause assigns one or more columns. The WHERE clause restricts the update to a subset of rows; if WHERE is omitted, every row in the table is updated, a not-infrequent source of accident. The recommendation is that interactive UPDATE against a production database is bracketed by BEGIN / ROLLBACK so that the row count can be inspected before commit:

BEGIN;
UPDATE customer SET email = 'ada@new.example.com' WHERE id = 1;
SELECT changes();   -- should be 1
COMMIT;

The changes() function returns the number of rows the most recent statement affected and is the conventional sanity check.

Multi-column SET

Multiple columns can be assigned in a single SET clause:

UPDATE customer
SET email = 'ada@new.example.com',
    phone = '+44 1234 567890',
    updated_at = strftime('%Y-%m-%dT%H:%M:%fZ', 'now')
WHERE id = 1;

The right-hand side may reference columns of the row — old values, before the update — which permits self-referential updates:

UPDATE bouquet SET price = price * 1.10 WHERE category = 'standard';

UPDATE OR

The UPDATE OR modifier specifies a conflict-resolution strategy for the statement:

UPDATE OR IGNORE customer SET email = ? WHERE id = ?;
UPDATE OR REPLACE customer SET email = ? WHERE id = ?;

The strategies are the same as for INSERT: ABORT (default), ROLLBACK, FAIL, IGNORE, REPLACE.

UPDATE FROM

An UPDATE may join against another table for the source values, using the UPDATE … FROM form added in 3.33:

UPDATE customer
SET email = source.new_email
FROM email_change AS source
WHERE customer.id = source.customer_id;

The form is convenient for bulk updates where the new values come from another table; without it, the conventional alternative was a correlated subquery in the SET clause:

UPDATE customer
SET email = (SELECT new_email FROM email_change WHERE customer_id = customer.id)
WHERE id IN (SELECT customer_id FROM email_change);

The UPDATE … FROM form is more readable and frequently faster.

DELETE

The DELETE statement removes rows:

DELETE FROM customer WHERE id = 1;

The WHERE clause restricts the deletion; if omitted, every row in the table is deleted. SQLite has no TRUNCATE statement; the conventional substitute is DELETE FROM table with no WHERE, which the engine optimises into a single B-tree truncation when there are no triggers and no foreign-key cascades:

DELETE FROM scratch;

This is called the truncate optimisation; it is fast (an O(1) metadata operation rather than an O(n) row-by-row delete) but is bypassed if the table has any BEFORE DELETE or AFTER DELETE trigger or if foreign-key cascades are active.

DELETE returns the number of rows affected through changes(), as UPDATE does:

DELETE FROM customer WHERE deleted_at < date('now', '-1 year');
SELECT changes();

RETURNING

The RETURNING clause, added in version 3.35 (2021), returns columns of the affected rows from INSERT, UPDATE, and DELETE statements. It is the SQLite analogue of PostgreSQL’s RETURNING and SQL Server’s OUTPUT.

INSERT INTO customer (name, email) VALUES ('Ada Lovelace', 'ada@example.com')
RETURNING id, name, created_at;
-- 1|Ada Lovelace|2026-05-08T14:01:23.456Z

The clause is particularly valuable for INSERT, where it eliminates the round-trip needed to fetch the auto-assigned id:

INSERT INTO event (payload) VALUES (?) RETURNING id, occurred_at;

RETURNING is also useful in DELETE statements that need to log the rows being removed:

DELETE FROM bouquet_order WHERE placed_at < date('now', '-1 year')
RETURNING id, customer_id, placed_at;

The returned rows can be consumed like a SELECT’s output by the language binding’s cursor or by the shell.

LIMIT on UPDATE and DELETE

The optional clauses ORDER BY and LIMIT may be added to UPDATE and DELETE statements when the SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option is enabled. The official SQLite distributions enable it by default; the form is widely available.

DELETE FROM event WHERE level = 'debug' ORDER BY occurred_at LIMIT 1000;

The form is useful for batched cleanups: deleting old rows in chunks without locking the table for too long. The conventional pattern is a loop in the application that deletes one batch per iteration until changes() returns zero.

Bulk operations and transactions

Each INSERT, UPDATE, or DELETE is its own implicit transaction unless wrapped in a BEGIN / COMMIT. For bulk operations, an explicit transaction is dramatically faster: the engine performs a single fsync at commit rather than one per statement.

BEGIN;
INSERT INTO event (payload) VALUES (?);
INSERT INTO event (payload) VALUES (?);
…
COMMIT;

A binding’s executemany — cursor.executemany('INSERT …', rows) in Python, INSERT INTO … VALUES (?), (?), … constructed in Node — is the conventional shape; see the language binding’s documentation for the exact API.

The performance difference is enormous: a million single-row inserts without transaction wrapping might take many minutes; the same inside one transaction takes seconds. The reason is the WAL (or rollback-journal) commit cost, which dominates at one fsync per row. Always wrap bulk modifications in an explicit transaction.

Idempotency and errors

A failure during an UPDATE, INSERT, or DELETE rolls back the statement’s effect: SQLite treats statements as atomic. A failure inside a transaction does not roll back the transaction; the transaction must be either committed (preserving prior changes) or rolled back manually. The recommended pattern is to wrap the entire unit of work in BEGIN / COMMIT and to use SAVEPOINT for sub-units that may need to be rolled back independently:

BEGIN;
SAVEPOINT s1;
INSERT INTO customer (name, email) VALUES (?, ?);
-- if duplicate email
ROLLBACK TO SAVEPOINT s1;
RELEASE SAVEPOINT s1;
COMMIT;

Transactions are documented in detail under Transactions and WAL.

Practical patterns

A short catalogue of patterns that recur in application code:

Insert-or-get

The application needs an existing row’s id, or a new row’s id if no matching row exists:

INSERT INTO tag (name) VALUES (?) ON CONFLICT (name) DO UPDATE SET name = name
RETURNING id;

The DO UPDATE SET name = name is a no-op that exists solely so the row’s id can be returned; without the update, RETURNING returns no row when the conflict path is taken.

Soft delete

A row is marked deleted but not removed:

UPDATE customer SET deleted_at = strftime('%Y-%m-%dT%H:%M:%fZ', 'now') WHERE id = ?;

Reads then filter on WHERE deleted_at IS NULL (frequently encapsulated in a view).

Counter increment

A counter is incremented atomically:

UPDATE counter SET value = value + 1 WHERE name = ? RETURNING value;

The RETURNING clause makes this a single round trip; without it, two statements (the update and a separate SELECT) are needed.

Bulk import with constraint resolution

A large set of rows from an external source is loaded with conflict-handling:

BEGIN;
INSERT INTO product (sku, name, price)
SELECT sku, name, price FROM imported
ON CONFLICT (sku) DO UPDATE SET
    name  = excluded.name,
    price = excluded.price;
COMMIT;

The pattern handles inserts and updates uniformly and is the canonical shape for nightly data refreshes.