SQLite DELETE: Remove Rows Safely with WHERE and RETURNING

DELETE Removes Rows, Nothing Else

DELETE takes rows out of a table. It doesn't drop the table, doesn't change its schema, doesn't touch other tables (unless you've set up cascades). The shape is short:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    active INTEGER NOT NULL DEFAULT 1
);

INSERT INTO users (email, active) VALUES
    ('ada@example.com', 1),
    ('boris@example.com', 0),
    ('rosa@example.com', 1);

DELETE FROM users WHERE id = 2;

SELECT * FROM users;

DELETE FROM users WHERE id = 2; finds rows matching the condition and removes them. The other two rows are untouched. The table itself still exists — you can keep inserting into it.

The mental model: DELETE is SELECT that throws the matching rows away instead of returning them.

The WHERE Clause Is Doing All the Work

Every serious DELETE lives or dies by its WHERE clause. Get it right and you remove what you meant to. Get it wrong and you delete more — sometimes all — of the table.

CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    title TEXT,
    published INTEGER NOT NULL DEFAULT 0,
    views INTEGER NOT NULL DEFAULT 0
);

INSERT INTO posts (title, published, views) VALUES
    ('Draft one',   0, 0),
    ('Old hit',     1, 5000),
    ('Published',   1, 120),
    ('Empty draft', 0, 0);

DELETE FROM posts WHERE published = 0 AND views = 0;

SELECT * FROM posts;

Both unpublished, zero-view drafts are gone. The published rows survive because the condition didn't match them. You can use any expression WHERE accepts — IN, LIKE, BETWEEN, subqueries, AND/OR combos.

A habit worth forming: before you run a DELETE, run the same WHERE clause as a SELECT first.

-- Preview what will go:
SELECT * FROM posts WHERE published = 0 AND views = 0;

-- Happy with the rows? Now delete them:
DELETE FROM posts WHERE published = 0 AND views = 0;

That two-step dance has saved more databases than every backup tool combined.

DELETE Without WHERE Empties the Table

Leave off WHERE and DELETE removes every row:

CREATE TABLE log (
    id INTEGER PRIMARY KEY,
    message TEXT
);

INSERT INTO log (message) VALUES ('one'), ('two'), ('three');

DELETE FROM log;

SELECT COUNT(*) AS remaining FROM log;

The table is empty but still exists. SQLite has no TRUNCATE statement — DELETE FROM table; is the equivalent, and SQLite applies an internal "truncate optimization" that drops all pages at once instead of removing rows individually. Fast, but still a transactional operation you can roll back.

If you used AUTOINCREMENT on the primary key, the counter doesn't reset on its own. To start ids back at 1, clear the sequence row too:

DELETE FROM log;
DELETE FROM sqlite_sequence WHERE name = 'log';

For plain INTEGER PRIMARY KEY (without AUTOINCREMENT), SQLite already reuses ids freely, so this isn't needed.

Deleting Multiple Specific Rows

IN is the cleanest way to delete a known set of rows:

CREATE TABLE tasks (
    id INTEGER PRIMARY KEY,
    title TEXT,
    status TEXT
);

INSERT INTO tasks (title, status) VALUES
    ('write docs',   'done'),
    ('review PR',    'todo'),
    ('deploy',       'done'),
    ('write tests',  'todo'),
    ('archive',      'done');

DELETE FROM tasks WHERE id IN (1, 3, 5);

SELECT * FROM tasks;

You can also drive a delete from a subquery — handy when the rows to remove are defined by a join or another table:

CREATE TABLE tasks (
    id INTEGER PRIMARY KEY,
    title TEXT,
    status TEXT
);
CREATE TABLE archived_status (status TEXT);

INSERT INTO tasks (title, status) VALUES
    ('a', 'done'), ('b', 'todo'), ('c', 'cancelled');
INSERT INTO archived_status VALUES ('done'), ('cancelled');

DELETE FROM tasks
WHERE status IN (SELECT status FROM archived_status);

SELECT * FROM tasks;

SQLite doesn't support DELETE ... JOIN syntax the way MySQL does, but a subquery in WHERE does the same job.

RETURNING: See What You Deleted

Add RETURNING to get the deleted rows back as a result set, just like a SELECT:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT,
    active INTEGER NOT NULL DEFAULT 1
);

INSERT INTO users (email, active) VALUES
    ('ada@example.com',   1),
    ('boris@example.com', 0),
    ('carol@example.com', 0);

DELETE FROM users
WHERE active = 0
RETURNING id, email;

You get back the id and email of every deleted row. This is invaluable for:

• Logging exactly what was removed.
• Building undo features (stash the returned rows somewhere).
• Confirming a delete affected the rows you expected, in a single round trip.

RETURNING works on INSERT, UPDATE, and DELETE. It's covered in detail on its own page.

ON DELETE CASCADE for Related Rows

When parent and child tables are linked by a foreign key, deleting the parent leaves orphaned children — unless you tell SQLite to cascade:

PRAGMA foreign_keys = ON;

CREATE TABLE authors (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author_id INTEGER REFERENCES authors(id) ON DELETE CASCADE
);

INSERT INTO authors (id, name) VALUES (1, 'Le Guin'), (2, 'Borges');
INSERT INTO books (title, author_id) VALUES
    ('A Wizard of Earthsea', 1),
    ('The Dispossessed',     1),
    ('Ficciones',            2);

DELETE FROM authors WHERE id = 1;

SELECT * FROM books;

Deleting the author also deletes their books. Without ON DELETE CASCADE, the same DELETE would either succeed and leave orphaned books (if foreign keys are off) or fail with a constraint error (if they're on).

The big footgun: foreign keys are off by default in SQLite. You have to run PRAGMA foreign_keys = ON; for every connection. If the pragma isn't set, ON DELETE CASCADE is silently ignored — the books stay. Most application drivers either set this for you or expose an option; check yours.

Other cascade options worth knowing: ON DELETE SET NULL (clear the foreign key), ON DELETE RESTRICT (refuse the delete if children exist), ON DELETE NO ACTION (the default — same as RESTRICT in most cases).

DELETE With LIMIT (Compile-Time Option)

Some SQLite builds support DELETE ... LIMIT, useful for chipping away at huge tables in batches:

DELETE FROM logs
WHERE created_at < '2024-01-01'
ORDER BY created_at
LIMIT 1000;

This requires SQLite to be compiled with SQLITE_ENABLE_UPDATE_DELETE_LIMIT. The official binaries and most language bindings (Python's sqlite3, Node's better-sqlite3) have it enabled. If yours doesn't, you'll get a syntax error — fall back to a subquery:

DELETE FROM logs
WHERE id IN (
    SELECT id FROM logs
    WHERE created_at < '2024-01-01'
    ORDER BY created_at
    LIMIT 1000
);

Batched deletes keep transactions small, which matters when other connections are reading the database.

Wrap Big Deletes in a Transaction

A DELETE is implicitly transactional — either every matching row goes or none do. But when you're about to delete a lot, wrapping it in an explicit transaction means you can ROLLBACK if something looks off:

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    balance INTEGER
);

INSERT INTO accounts (balance) VALUES (100), (200), (0), (50), (0);

BEGIN;
    DELETE FROM accounts WHERE balance = 0;
    SELECT COUNT(*) AS remaining FROM accounts;
ROLLBACK;

SELECT COUNT(*) AS still_here FROM accounts;

ROLLBACK undoes the delete entirely. In a real session, you'd COMMIT once the count looks right. Transactions are also dramatically faster when deleting many rows one statement at a time — wrapping the loop in BEGIN/COMMIT avoids one fsync per delete.

Things That Don't Delete

A few common confusions worth flagging:

• DELETE FROM table; empties the table but doesn't drop it. Use DROP TABLE table; to remove the table itself.
• DELETE doesn't shrink the database file. Pages are marked free for reuse. To reclaim disk space, run VACUUM; (covered in the performance chapter).
• Deleting a row doesn't delete child rows in other tables unless ON DELETE CASCADE is set and foreign keys are enabled.
• A DELETE that matches zero rows is not an error. It's a successful statement with changes() = 0. Check the row count if you need to know.

Next: UPSERT

Often you don't actually want to delete — you want to insert if a row is new, or update if it already exists. SQLite calls that an UPSERT, and the ON CONFLICT clause makes it one statement instead of three. That's next.