MySQL DELETE Statement

MySQL DELETE Statement: Removing Table Rows Safely

The MySQL DELETE statement permanently removes rows from a table that match your WHERE condition. Unlike dropping a table, DELETE keeps the table schema, indexes, and permissions intact.

Basic Syntax

Never run DELETE FROM table_name; unless you want to empty the entire table row by row:

DELETE FROM table_name
WHERE condition;

Practical SQL Example

This query deletes cancelled orders created before 2025 from demo_orders inside a safe condition.

-- Preview target rows first
SELECT id, customer_id, status, created_at
FROM demo_orders
WHERE status = 'cancelled'
  AND created_at < '2025-01-01';

-- Remove only matching rows
DELETE FROM demo_orders
WHERE status = 'cancelled'
  AND created_at < '2025-01-01';

DELETE vs. TRUNCATE TABLE

• DELETE FROM table_name: Logs every row deletion in the undo/redo log, fires DELETE triggers, preserves the AUTO_INCREMENT counter, and can be rolled back inside a transaction.
• TRUNCATE TABLE table_name: Drops and recreates the table storage immediately, resets AUTO_INCREMENT to 1, and cannot be filtered with WHERE.

Common Mistakes and Best Practices

• Prefer Soft Deletes for Audit Data: Add a deleted_at TIMESTAMP NULL column on business tables like orders and users so historical invoices stay intact.
• Batch Large Cleanups: Deleting millions of rows in one statement locks rows and fills the transaction log. Loop with DELETE FROM demo_orders WHERE ... LIMIT 5000; until zero rows remain.

Frequently Asked Questions

Can I undo a DELETE statement in MySQL?

Only if you ran it inside an explicit START TRANSACTION block and have not called COMMIT yet; after COMMIT, recovery requires a backup or binary log replay.

Does DELETE reset the AUTO_INCREMENT ID counter?

No, DELETE keeps the current AUTO_INCREMENT sequence so newly inserted rows never reuse deleted primary keys.

Why does DELETE fail with a foreign key error?

Another table references the rows you tried to delete; delete or unlink those child rows first unless ON DELETE CASCADE is configured.