SQL DELETE
Remove rows from a table — carefully.
Basic syntax
SQL
DELETE FROM customers
WHERE id = 7;
ALWAYS include WHERE
DELETE FROM customers; without a WHERE clause deletes every row in the table instantly. Unlike dropping a table, this respects foreign key constraints but the data is gone.
Delete with multiple conditions
SQL
DELETE FROM orders
WHERE amount < 10
AND order_date < '2023-01-01';
Delete using a subquery
SQL — delete orders from inactive customers
DELETE FROM orders
WHERE customer_id IN (
SELECT id FROM customers
WHERE is_active = FALSE
);
DELETE vs TRUNCATE
| Aspect | DELETE | TRUNCATE |
|---|---|---|
| WHERE clause? | Yes — selective or full | No — always all rows |
| Speed (large tables) | Slow — row-by-row | Very fast — page deallocate |
| Rollback? | Yes (in a transaction) | No in most DBs |
| Triggers? | Fires row-level triggers | Does not fire triggers |
| Resets identity? | No | Yes (auto-increment back to 1) |
| FK constraints? | Respected | Usually requires FK drop first |
Soft delete pattern (recommended)
Instead of physically removing rows, mark them as deleted. Safer for audit trails:
SQL — soft delete
-- Add a deleted_at column (NULL = active)
UPDATE customers
SET deleted_at = NOW()
WHERE id = 7;
-- All future queries filter it out
SELECT * FROM customers WHERE deleted_at IS NULL;
Safe DELETE pattern
Run the equivalent SELECT first:
SELECT * FROM orders WHERE amount < 10; — verify the rows you're about to delete. Then swap SELECT * for DELETE FROM. Same WHERE, zero surprises.