SQLite DELETE

The DELETE statement removes rows from a table. Unlike DROP TABLE, which destroys the entire table, DELETE removes only the matching rows while keeping the table structure intact and ready for new data.

Sample Table

orders
──────────────────────────────────────────────────────
id │ customer   │ product     │ amount │ status
───┼────────────┼─────────────┼────────┼──────────
1  │ Alice      │ Notebook    │ 2.99   │ delivered
2  │ Bob        │ Backpack    │ 24.99  │ cancelled
3  │ Carol      │ Pen         │ 0.99   │ delivered
4  │ Dan        │ Eraser      │ 0.59   │ cancelled
5  │ Eve        │ Water Bottle│ 8.50   │ pending

Basic DELETE Syntax

DELETE FROM table_name
WHERE condition;

Delete One Row

DELETE FROM orders WHERE id = 4;

-- Before: 5 rows
-- After:  4 rows (row with id=4 removed)

id │ customer │ product      │ amount │ status
───┼──────────┼──────────────┼────────┼──────────
1  │ Alice    │ Notebook     │ 2.99   │ delivered
2  │ Bob      │ Backpack     │ 24.99  │ cancelled
3  │ Carol    │ Pen          │ 0.99   │ delivered
5  │ Eve      │ Water Bottle │ 8.50   │ pending

Delete Multiple Rows

-- Remove all cancelled orders
DELETE FROM orders WHERE status = 'cancelled';

-- Removes Bob's and Dan's orders (both were cancelled)

Delete All Rows (No WHERE)

Omitting WHERE removes every row from the table. The table itself remains — just empty. This is equivalent to TRUNCATE in other databases.

-- DANGER: removes all orders
DELETE FROM orders;

-- Table is now empty but still exists:
sqlite> .tables
orders      ← still here

sqlite> SELECT COUNT(*) FROM orders;
0           ← no rows

DELETE with IN — Multiple Specific IDs

-- Delete orders with specific IDs
DELETE FROM orders WHERE id IN (2, 4);

-- Alternatively, delete multiple status types:
DELETE FROM orders
WHERE status IN ('cancelled', 'returned');

DELETE with Subquery

A subquery inside DELETE lets you remove rows based on data from another table.

-- Delete orders placed by inactive customers
DELETE FROM orders
WHERE customer IN (
  SELECT name FROM customers WHERE active = 0
);

Checking How Many Rows Were Deleted

DELETE FROM orders WHERE status = 'cancelled';

SELECT changes();
-- Returns: 2  (two cancelled orders removed)

Safe DELETE Workflow

Step 1: SELECT the rows you plan to delete
──────────────────────────────────────────────
SELECT id, customer, status FROM orders
WHERE status = 'cancelled';

   id │ customer │ status
   ───┼──────────┼──────────
   2  │ Bob      │ cancelled
   4  │ Dan      │ cancelled

Step 2: Confirm these are correct rows

Step 3: Run DELETE with identical WHERE clause
──────────────────────────────────────────────
DELETE FROM orders WHERE status = 'cancelled';

Step 4: Verify
──────────────────────────────────────────────
SELECT COUNT(*) FROM orders WHERE status = 'cancelled';
-- Should return: 0

Using a Transaction for Safety

BEGIN;

DELETE FROM orders WHERE status = 'cancelled';

-- Check how many rows were affected
SELECT changes();  -- should be 2

-- If correct:
COMMIT;

-- If something went wrong:
ROLLBACK;

DELETE vs DROP TABLE vs UPDATE

OPERATION                   RESULT
──────────────────────────────────────────────────────────
DELETE FROM orders          Removes rows; table stays
  WHERE status='cancelled'  Only cancelled rows removed

DELETE FROM orders          Removes ALL rows; table stays

DROP TABLE orders           Table and ALL data destroyed

UPDATE orders               Changes values in rows;
  SET status='archived'     rows remain in table

What Happens to the Auto-Increment After DELETE

Deleting rows does not reset the auto-increment counter. If you delete the row with id=5 and then insert a new row, SQLite assigns id=6, not id=5.

sqlite> SELECT MAX(id) FROM orders;
5

DELETE FROM orders WHERE id = 5;

INSERT INTO orders (customer, product, amount, status)
VALUES ('Frank', 'Ruler', 1.49, 'pending');

sqlite> SELECT id FROM orders ORDER BY id DESC LIMIT 1;
6   ← not 5, counter continues from last max

Key Takeaways

• DELETE FROM table WHERE condition removes matching rows
• Omitting WHERE deletes every row — always write the WHERE clause first
• Use SELECT with the same WHERE condition before running DELETE
• Wrap critical deletes in a transaction to allow rollback if needed
• Auto-increment IDs do not reset after row deletion