DELETE

DELETE removes rows from a table. Like UPDATE, its WHERE clause decides which rows are affected, and leaving it out affects every row. This lesson also covers the alternatives: emptying tables quickly, and "soft" deletes that keep history.

Deleting specific rows

SQL 
DELETE FROM order_items
WHERE order_id = 5;

SELECT COUNT(*) AS items_left_in_order_5 FROM order_items WHERE order_id = 5;
Result 1 row
items_left_in_order_5
0

Deleting with conditions and subqueries

SQL 
DELETE FROM order_items
WHERE order_id IN (SELECT id FROM orders WHERE status = 'cancelled');

DELETE FROM orders WHERE status = 'cancelled';

SELECT status, COUNT(*) AS orders FROM orders GROUP BY status;
Result 3 rows
statusorders
delivered9
pending3
shipped2

Foreign keys protect related rows

SQL 
DELETE FROM customers WHERE id = 1;
Result
Error: FOREIGN KEY constraint failed

Ana has orders, and those orders reference her id. Deleting her would leave orphaned orders pointing at nobody, so the foreign-key constraint blocks it. You'd need to delete or reassign her orders first — or define the key with ON DELETE CASCADE (lesson 43). (SQLite only enforces foreign keys when PRAGMA foreign_keys = ON, which this course's editors switch on.)

SQL 
DELETE FROM customers WHERE id = 10;

SELECT COUNT(*) AS customers FROM customers;
Result 1 row
customers
9

Julia has no orders, so she can be deleted.

Deleting everything

SQL 
DELETE FROM order_items;

SELECT COUNT(*) AS remaining FROM order_items;
Result 1 row
remaining
0

Without WHERE, all rows go; the table itself remains. Most databases also have TRUNCATE TABLE order_items, which empties a table much faster for large tables (it doesn't delete row by row), but it can't have a WHERE and behaves differently with triggers and transactions in some systems. SQLite has no TRUNCATE; it optimises DELETE without WHERE instead.

DROP TABLE is different again: it removes the table entirely, structure and all (lesson 44).

Soft deletes

SQL 
ALTER TABLE customers ADD COLUMN deleted_at TEXT;

UPDATE customers SET deleted_at = '2026-03-15' WHERE id = 7;

SELECT name FROM customers WHERE deleted_at IS NULL AND country = 'UK';
Result 1 row
name
Ben Carter

Many applications never truly delete important records. Instead they mark them as deleted with a timestamp, and normal queries filter them out. This keeps history (George's orders still make sense), allows "undo", and supports audits — at the cost of remembering the filter everywhere (a view helps, lesson 47).

Safe deleting habits

  • Run the same WHERE with SELECT first and check the rows.
  • Delete by primary key when you mean one row.
  • Use a transaction so a mistake can be rolled back (lesson 39).
  • Back up before bulk deletes in production.

Try it yourself

Delete products that have never been ordered and have no stock — then try deleting all products that have never been ordered.

Try it yourself
Result
PreviousUPDATE