DELETE

Explanation

DELETE removes rows from a table. Like UPDATE, it is scoped by a WHERE clause — and like UPDATE, forgetting the WHERE deletes every row.

DELETE removes data but keeps the table. To remove the table itself you'd use DROP TABLE, which is a different, more destructive command.

Syntax

DELETE FROM orders
WHERE status = 'cancelled';
 
-- Delete rows matching another table:
DELETE FROM orders
WHERE customer_id IN (
  SELECT id FROM customers WHERE is_active = false
);

Interactive Example

Preview how many orders are cancelled (the rows a DELETE would remove), and list the specific cancelled orders before acting.

Common Mistakes

• Missing WHERE. Deletes all rows; use a transaction so you can recover.
• Confusing DELETE with DROP TABLE. DROP removes the table itself — there's no "undo" without a backup.
• Deleting without checking references. If other tables reference these rows (foreign keys), the delete may fail or cascade — know your constraints.

Best Practices

• Always preview with SELECT COUNT(*) using the same WHERE.
• Run deletes inside a transaction; COMMIT only after verifying.
• Consider a soft delete (set an is_deleted flag) for recoverable data.
• Be aware of foreign-key cascade rules before deleting parent rows.

Practice Question

Write a DELETE that removes every order with status = 'pending' placed before '2022-03-01', and describe the SELECT you'd run first to preview the count.

Summary

DELETE removes rows, optionally filtered by WHERE. It keeps the table structure (unlike DROP TABLE), but a missing WHERE wipes all rows — so always preview the count and wrap important deletes in a transaction.

FAQ