DELETE with WHERE Clause

The WHERE clause in a DELETE statement is what makes it surgical. By specifying precise conditions, you can remove exactly the rows you want without affecting others. This is the most important aspect of safe data deletion.

Filtering Rows for Deletion

The WHERE clause supports the full range of SQL conditions: equality checks, ranges with BETWEEN, list membership with IN, pattern matching with LIKE, and combined conditions with AND/OR. You can filter by any column, not just the primary key.

Common scenarios include removing expired records, cleaning up test data, or archiving old orders by deleting them from the active table after copying them to an archive table.

Safety Tip

Run a SELECT COUNT(*) with the same WHERE to see how many rows will be deleted
Run a SELECT * with the same WHERE to inspect the rows
Use a transaction so you can rollback
Consider LIMIT to cap accidental mass deletions

Example

-- Delete orders older than 2 years with status "cancelled"
DELETE FROM orders
WHERE status = 'cancelled'
  AND created_at < DATE_SUB(NOW(), INTERVAL 2 YEAR);

SQL

Full Editor

Pro Tip

Verify your WHERE clause with SELECT before running DELETE.

Related Resources

MySQL Reference

Complete tag & property list

MySQL How-To Guides

Step-by-step practical guides

MySQL Exercises

Practice what you've learned