Removes rows from a table. Use a WHERE clause to target specific rows; omitting it deletes every row. To remove the table itself, use DROP TABLE.
Syntax
DELETE FROM table_name
[WHERE expression]
[RETURNING result_column [, ...]]
[ORDER BY sort_expression [, ...]]
[LIMIT expression [OFFSET expression]]
Parameters
| Parameter | Description |
|---|
table_name | The table from which rows are removed. |
expression | Filter condition. Only rows where this evaluates to true are deleted. |
result_column | Column or expression to return for each deleted row. |
sort_expression | Determines the order rows are considered when combined with LIMIT. |
WHERE clause
Restricts which rows are deleted. Without a WHERE clause, every row in the table is deleted.
DELETE FROM logs WHERE created_at < date('now', '-90 days');
RETURNING clause
Causes the DELETE to return the values of each deleted row. 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
ORDER BY determines the order in which rows are considered, and LIMIT caps the number of rows 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 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. 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
Delete all rows
-- Removes every row; the table structure is preserved
DELETE FROM staging_data;
See also