DELETE
The DELETE statement removes rows from a table.
Syntax
DELETE FROM table-name
[WHERE expression]
[RETURNING result-column [, result-column ...]]
[ORDER BY sort-expression [, sort-expression ...]]
[LIMIT expression [OFFSET expression]]
Description
DELETE removes rows that match the WHERE condition. If no WHERE clause is provided, all rows in the table are deleted. The table itself is not removed; use DROP TABLE to remove a table entirely.
WHERE Clause
The WHERE clause restricts which rows are deleted. Only rows where the expression evaluates to true are removed. Without a WHERE clause, every row in the table is deleted.
DELETE FROM logs WHERE created_at < date('now', '-90 days');
RETURNING Clause
The RETURNING clause causes the DELETE statement to return the values of each deleted row. The result columns can be any expression referencing the deleted row’s columns.
DELETE FROM sessions
WHERE expires_at < datetime('now')
RETURNING session_id, user_id;
ORDER BY and LIMIT
The ORDER BY and LIMIT clauses restrict which rows are deleted. ORDER BY determines the order in which rows are considered, and LIMIT caps the number of rows that are actually removed. ORDER BY requires LIMIT to be present.
DELETE FROM logs
ORDER BY created_at ASC
LIMIT 1000;
LIMIT accepts an optional OFFSET to skip a number of rows before applying the limit:
DELETE FROM logs
ORDER BY created_at ASC
LIMIT 500 OFFSET 100;
ORDER BY without LIMIT is not allowed on DELETE statements. If you need to delete all rows matching a condition in a specific order, provide a LIMIT value larger than the expected number of matching rows.
Examples
Delete rows matching a condition
CREATE TABLE tasks (id INTEGER PRIMARY KEY, title TEXT, completed INTEGER);
INSERT INTO tasks VALUES (1, 'Write docs', 1);
INSERT INTO tasks VALUES (2, 'Review PR', 0);
INSERT INTO tasks VALUES (3, 'Fix bug', 1);
DELETE FROM tasks WHERE completed = 1;
SELECT * FROM tasks;
-- 2|Review PR|0
Delete with RETURNING
CREATE TABLE queue (id INTEGER PRIMARY KEY, payload TEXT, priority INTEGER);
INSERT INTO queue VALUES (1, 'job_a', 10);
INSERT INTO queue VALUES (2, 'job_b', 5);
INSERT INTO queue VALUES (3, 'job_c', 10);
DELETE FROM queue
WHERE id = (SELECT id FROM queue ORDER BY priority DESC, id ASC LIMIT 1)
RETURNING id, payload;
-- 1|job_a
Delete the oldest rows with ORDER BY and LIMIT
CREATE TABLE events (id INTEGER PRIMARY KEY, message TEXT, created_at TEXT);
INSERT INTO events VALUES (1, 'startup', '2025-01-01');
INSERT INTO events VALUES (2, 'request', '2025-01-02');
INSERT INTO events VALUES (3, 'shutdown', '2025-01-03');
INSERT INTO events VALUES (4, 'startup', '2025-01-04');
DELETE FROM events
ORDER BY created_at ASC
LIMIT 2;
SELECT * FROM events;
-- 3|shutdown|2025-01-03
-- 4|startup|2025-01-04
See Also
