SQLite DELETE: How to Delete Rows from a Table (Tested Examples)

Ninad Pathak

October 7, 2026

Deleting rows in SQLite has no undo and no confirmation prompt, so the WHERE clause on a DELETE decides everything. In this article I’ll walk you through deleting exactly the rows you mean, from one row by ID to deletes you can roll back.

TLDR

DELETE FROM orders WHERE id = 3; deletes one row. The WHERE clause decides which rows go.
DELETE FROM orders; with no WHERE deletes every row but keeps the table. SQLite has no TRUNCATE.

Run the same WHERE in a SELECT first, or add RETURNING to see what was deleted.
Wrap risky deletes in BEGIN … ROLLBACK/COMMIT so you can undo them.
SQLite can’t DELETE … JOIN. Use WHERE id IN (SELECT …) instead.

What is the SQLite DELETE statement?

DELETE removes rows from one table. SQLite’s docs: “If the WHERE clause is not present, all records in the table are deleted. If a WHERE clause is supplied, then only those rows for which the WHERE clause boolean expression is true are deleted” (SQLite DELETE). It never removes the table itself; that’s DROP TABLE.

SQLite DELETE syntax

DELETE FROM table_name
WHERE condition
RETURNING columns;

Part	Required	What it does
`DELETE FROM table_name`	Yes	The one table to delete from
`WHERE condition`	No	Which rows to delete. Without it, every row goes
`RETURNING columns`	No	Returns the deleted rows (SQLite 3.35.0 and later)

How to delete rows in SQLite

To show you how DELETE behaves, I will use a small shop database with two tables. Every example below starts from a fresh copy of it:

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, status TEXT NOT NULL);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
  total REAL NOT NULL,
  placed_on TEXT NOT NULL
);
INSERT INTO customers (name, status) VALUES
  ('Ada', 'active'), ('Linus', 'inactive'), ('Grace', 'active'), ('Ken', 'inactive');
INSERT INTO orders (customer_id, total, placed_on) VALUES
  (1, 20.00, '2026-01-15'), (1, 35.50, '2026-09-02'), (2, 12.00, '2025-11-30'),
  (3, 99.90, '2026-08-21'), (4, 5.00, '2025-06-01');

sqlite> SELECT * FROM customers;
id  name   status
--  -----  --------
1   Ada    active
2   Linus  inactive
3   Grace  active
4   Ken    inactive
sqlite> SELECT * FROM orders;
id  customer_id  total  placed_on
--  -----------  -----  ----------
1   1            20.0   2026-01-15
2   1            35.5   2026-09-02
3   2            12.0   2025-11-30
4   3            99.9   2026-08-21
5   4            5.0    2025-06-01

Step 1: Preview the rows with SELECT

Write the WHERE clause as a SELECT first. These are the rows a DELETE with the same condition would remove:

sqlite> SELECT id, total FROM orders WHERE total < 15;
id  total
--  -----
3   12.0
5   5.0

Step 2: Run the DELETE with the same WHERE

RETURNING shows exactly which rows went:

sqlite> DELETE FROM orders WHERE total < 15 RETURNING id, total;
id  total
--  -----
3   12.0
5   5.0

Step 3: Check how many rows changed

changes() returns the number of rows the last statement deleted. If nothing matched, it’s 0. That’s not an error:

sqlite> DELETE FROM orders WHERE id = 99; SELECT changes() AS deleted;
deleted
-------
0

Examples

Each example starts from the same fresh database, so the outputs don’t depend on each other.

Delete a row by ID

sqlite> DELETE FROM orders WHERE id = 3; SELECT changes() AS deleted;
deleted
-------
1
sqlite> SELECT id FROM orders;
id
--
1
2
4
5

Delete rows that match a condition

Here, every order placed before 2026:

sqlite> DELETE FROM orders WHERE placed_on < '2026-01-01'; SELECT changes() AS deleted;
deleted
-------
2
sqlite> SELECT id, placed_on FROM orders;
id  placed_on
--  ----------
1   2026-01-15
2   2026-09-02
4   2026-08-21

Delete multiple rows by ID

sqlite> DELETE FROM orders WHERE id IN (1, 2, 5); SELECT changes() AS deleted;
deleted
-------
3
sqlite> SELECT id FROM orders;
id
--
3
4

Combine conditions with AND or OR

Only Ada’s orders under 30 go. See the AND operator and OR operator for more conditions:

sqlite> DELETE FROM orders WHERE customer_id = 1 AND total < 30; SELECT changes() AS deleted;
deleted
-------
1

Delete all rows from a table

Leave out WHERE, and every row goes, while the table stays:

sqlite> DELETE FROM orders; SELECT changes() AS deleted;
deleted
-------
5
sqlite> SELECT count(*) AS orders_left FROM orders;
orders_left
-----------
0
sqlite> SELECT name FROM sqlite_schema WHERE type = 'table';
name
---------
customers
orders

SQLite has no TRUNCATE statement:

sqlite> TRUNCATE TABLE orders;
Error: in prepare, near "TRUNCATE": syntax error
  TRUNCATE TABLE orders;
  ^--- error here

You don’t need one. When a DELETE has no WHERE and no RETURNING, and the table has no triggers, SQLite uses its “truncate optimization” and clears the table “without having to visit each row” (SQLite DELETE).

Delete rows based on another table

SQLite doesn’t accept a join in DELETE:

sqlite> DELETE FROM orders AS o JOIN customers AS c ON c.id = o.customer_id WHERE c.status = 'inactive';
Error: in prepare, near "JOIN": syntax error
  DELETE FROM orders AS o JOIN customers AS c ON c.id = o.customer_id WHERE c.st
                          ^--- error here

Put the other table in a subquery instead. This deletes the orders of inactive customers:

sqlite> DELETE FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE status = 'inactive'); SELECT changes() AS deleted;
deleted
-------
2

Delete from several tables at once

A single DELETE only touches one table. To remove a customer and their orders together, declare the foreign key with ON DELETE CASCADE (as setup.sql does) and turn foreign keys on. Deleting Ada then deletes her orders 1 and 2:

sqlite> PRAGMA foreign_keys = ON; DELETE FROM customers WHERE id = 1;
sqlite> SELECT id, customer_id FROM orders;
id  customer_id
--  -----------
3   2
4   3
5   4

Foreign keys are off by default, so set PRAGMA foreign_keys = ON on every connection. See SQLite foreign keys.

Delete the first row

To delete the oldest order, find it in a subquery:

sqlite> DELETE FROM orders WHERE id = (SELECT id FROM orders ORDER BY placed_on LIMIT 1) RETURNING id, placed_on;
id  placed_on
--  ----------
5   2025-06-01

DELETE … ORDER BY … LIMIT also works, but only “if SQLite is compiled with the SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option” (SQLite DELETE). The sqlite3 shell on macOS has that option, so this ran:

sqlite> DELETE FROM orders ORDER BY placed_on LIMIT 1; SELECT changes() AS deleted;
deleted
-------
1
sqlite> SELECT id, placed_on FROM orders ORDER BY placed_on LIMIT 1;
id  placed_on
--  ----------
3   2025-11-30
sqlite> SELECT compile_options FROM pragma_compile_options WHERE compile_options LIKE '%DELETE_LIMIT%';
compile_options
--------------------------
ENABLE_UPDATE_DELETE_LIMIT

Builds without that option reject the same statement with a syntax error, so the subquery is the portable form.

Undo a delete with a transaction

Inside BEGIN, the delete only becomes permanent on COMMIT. ROLLBACK brings every row back:

sqlite> BEGIN; DELETE FROM orders; SELECT count(*) AS inside_transaction FROM orders; ROLLBACK; SELECT count(*) AS after_rollback FROM orders;
inside_transaction
------------------
0
after_rollback
--------------
5

Delete a row from Python

Pass the ID as a parameter, then commit:

import sqlite3

con = sqlite3.connect("shop.db")
cur = con.execute("DELETE FROM orders WHERE id = ?", (4,))
print("rows deleted:", cur.rowcount)
con.commit()  # without this, the delete is rolled back when the connection closes
print("orders left:", con.execute("SELECT count(*) FROM orders").fetchone()[0])
con.close()

$ python3 delete_row.py
rows deleted: 1
orders left: 4

Edge cases

The file doesn’t shrink: deleted rows leave free pages inside the database file. Run VACUUM to give the space back.
The table doesn’t exist: DELETE has no IF EXISTS, so it fails:

sqlite> DELETE FROM invoices WHERE id = 1;
Error: in prepare, no such table: invoices

NULL in the condition: rows where the WHERE expression is NULL are kept, not deleted (SQLite DELETE). Use WHERE column IS NULL to delete them.

Conclusion

Preview with SELECT, then delete with the same WHERE. Add RETURNING when you want a record of what went, and wrap anything risky in a transaction. Use a subquery or ON DELETE CASCADE when rows depend on another table.