DELETE Without WHERE Clause (Production Disaster)

-- Intended to delete old orders, but forgot WHERE clause

DELETE FROM orders;

-- Deletes ALL orders (millions of rows gone in seconds)

-- Always use WHERE clause to specify which rows to delete

DELETE FROM orders WHERE order_date < '2023-01-01';

-- Or use transaction to test first

BEGIN;

DELETE FROM orders WHERE order_date < '2023-01-01';

SELECT COUNT(*) FROM orders; -- Verify count is correct

ROLLBACK; -- Undo if wrong

-- COMMIT; -- Commit if correct

DELETE without WHERE is the most common production disaster. It deletes all rows in the table instantly. Example: Developer intends to delete old orders (WHERE order_date < 2023-01-01) but forgets WHERE clause. Result: DELETE FROM orders; deletes all orders (millions of rows). Real-world: GitLab accidentally deleted 300GB of production data in 2017 due to wrong DELETE command. Prevention: (1) Always use WHERE in DELETE, (2) Test in transaction first (BEGIN, DELETE, verify, ROLLBACK/COMMIT), (3) Use database triggers to block DELETE without WHERE, (4) Require code review for DELETE statements, (5) Backup before bulk deletes. Many companies enforce WHERE clause requirement in code reviews and database policies.

Always use WHERE in DELETE. Test in transaction first: BEGIN, DELETE, verify count, ROLLBACK if wrong. Many companies block DELETE without WHERE using database triggers.

Critical

All rows deleted from orders table. Production data lost. Recovery requires restore from backup (hours of downtime).

graph TB

subgraph "Wrong: DELETE Without WHERE"

W1["DELETE FROM orders"]

W2["No WHERE clause"]

W3["Deletes ALL rows<br/>Millions of orders gone"]

W4["Production disaster<br/>Restore from backup"]

end

subgraph "Correct: DELETE With WHERE"

C1["DELETE FROM orders<br/>WHERE order_date < '2023-01-01'"]

C2["WHERE filters rows"]

C3["Deletes only old orders<br/>Recent orders safe"]

C4["Production safe"]

end

subgraph "Prevention"

P1["Test in transaction first"]

P2["BEGIN; DELETE; verify; ROLLBACK"]

P3["Backup before delete"]

P4["Code review for DELETE"]

end

W1 --> W2 --> W3 --> W4

C1 --> C2 --> C3 --> C4

P1 --> P2 --> P3 --> P4

style W3 fill:#ffcdd2

style W4 fill:#ffcdd2