MySQL DELETE: Complete Guide

Basic DELETE Syntax

The DELETE statement removes rows from a table:

DELETE FROM table_name
WHERE condition;

For example, deleting a specific user:

DELETE FROM users
WHERE id = 123;

The Cardinal Rule: Always Use WHERE

A DELETE without WHERE removes every row in the table:

-- DANGER: Deletes ALL users!
DELETE FROM users;

This is rarely what you want. Always include a WHERE clause:

DELETE FROM users
WHERE status = 'deleted';

Safety tip: In production, run a SELECT with the same WHERE clause first to verify which rows will be affected: SELECT * FROM users WHERE status = 'deleted'

DELETE with LIMIT

Limit how many rows are deleted—useful for batching large deletes:

DELETE FROM logs
WHERE created_at < '2023-01-01'
LIMIT 1000;

Run this repeatedly until no rows are affected. This prevents long-running transactions that lock the table.

DELETE with ORDER BY

Control which rows are deleted when using LIMIT:

-- Delete the 10 oldest inactive users
DELETE FROM users
WHERE last_login < '2022-01-01'
ORDER BY last_login ASC
LIMIT 10;

DELETE with JOIN

Delete rows based on data in another table:

-- Delete all orders for deleted users
DELETE orders
FROM orders
INNER JOIN users ON orders.user_id = users.id
WHERE users.status = 'deleted';

The table to delete from comes right after DELETE. The FROM clause specifies the joined tables.

Multi-Table DELETE

Delete from multiple tables in one statement:

-- Delete user and all their orders
DELETE users, orders
FROM users
INNER JOIN orders ON users.id = orders.user_id
WHERE users.id = 123;

DELETE vs TRUNCATE

For removing all rows, TRUNCATE is faster:

-- Slow: Logs each row deletion
DELETE FROM logs;

-- Fast: Drops and recreates the table
TRUNCATE TABLE logs;

| Feature | DELETE | TRUNCATE | |---------|--------|----------| | WHERE clause | Yes | No | | Triggers fire | Yes | No | | Can rollback | Yes | No (in most cases) | | Resets AUTO_INCREMENT | No | Yes | | Speed | Slow (row by row) | Fast |

Use TRUNCATE when:

• You want to remove ALL rows
• You don't need triggers to fire
• You want to reset AUTO_INCREMENT

Soft Delete Pattern

Instead of actually deleting data, mark it as deleted:

-- Add a column for soft deletes
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP NULL;

-- "Delete" by setting the timestamp
UPDATE users
SET deleted_at = NOW()
WHERE id = 123;

-- Query only non-deleted users
SELECT * FROM users WHERE deleted_at IS NULL;

Benefits:

• Recoverable: Can "undelete" by setting deleted_at back to NULL
• Audit trail: Know when something was deleted
• Referential integrity: Foreign keys still work

Downsides:

• Query complexity: Must filter deleted_at everywhere
• Storage: Deleted data still takes space
• Indexes: May need partial indexes

Safe Deletion Workflow

1. Preview what will be deleted:

SELECT * FROM orders
WHERE created_at < '2023-01-01'
  AND status = 'cancelled';

2. Count the rows:

SELECT COUNT(*) FROM orders
WHERE created_at < '2023-01-01'
  AND status = 'cancelled';

3. Back up if important:

CREATE TABLE orders_backup_20240115 AS
SELECT * FROM orders
WHERE created_at < '2023-01-01'
  AND status = 'cancelled';

4. Delete with LIMIT first:

DELETE FROM orders
WHERE created_at < '2023-01-01'
  AND status = 'cancelled'
LIMIT 100;

5. Verify, then delete the rest:

DELETE FROM orders
WHERE created_at < '2023-01-01'
  AND status = 'cancelled';

Foreign Key Constraints

DELETE can fail if foreign keys reference the row:

-- Error: Cannot delete parent row: a foreign key constraint fails
DELETE FROM users WHERE id = 123;

Options:

1. Delete child rows first

DELETE FROM orders WHERE user_id = 123;
DELETE FROM users WHERE id = 123;

2. Use ON DELETE CASCADE

-- When creating the table
CREATE TABLE orders (
  user_id INT,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Now deleting a user automatically deletes their orders
DELETE FROM users WHERE id = 123;

3. Use ON DELETE SET NULL

FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL

Performance Considerations

Large Deletes Lock Tables

Deleting millions of rows in one statement can lock the table for a long time:

-- Bad: Locks table for potentially minutes
DELETE FROM logs WHERE created_at < '2023-01-01';

Better approach—delete in batches:

-- Delete 10,000 at a time
REPEAT
  DELETE FROM logs
  WHERE created_at < '2023-01-01'
  LIMIT 10000;
UNTIL ROW_COUNT() = 0 END REPEAT;

Or use a simple script that loops until done.

Indexes Matter

Ensure your WHERE clause columns are indexed:

-- Slow without index on created_at
DELETE FROM logs WHERE created_at < '2023-01-01';

-- Add index if needed
CREATE INDEX idx_logs_created_at ON logs(created_at);

Quick Reference

| Command | Description | |---------|-------------| | DELETE FROM t WHERE ... | Delete matching rows | | DELETE FROM t WHERE ... LIMIT n | Delete up to n rows | | DELETE FROM t ORDER BY ... LIMIT n | Delete specific n rows | | DELETE t FROM t JOIN ... | Delete using JOIN | | TRUNCATE TABLE t | Remove all rows (fast) |

Common Mistakes

Forgetting WHERE

-- Deletes EVERYTHING
DELETE FROM users;

Wrong JOIN Syntax

-- Wrong: Tries to delete from both tables
DELETE FROM orders
JOIN users ON orders.user_id = users.id;

-- Right: Specify which table to delete from
DELETE orders
FROM orders
JOIN users ON orders.user_id = users.id;

Not Testing First

Always run SELECT with your WHERE clause before DELETE.

Summary

• Always use WHERE unless you want to delete all rows
• SELECT first to preview what will be deleted
• Use LIMIT for large deletes to avoid long locks
• Consider soft deletes for data you might need to recover
• Watch for foreign keys—they'll block deletes or cascade unexpectedly
• TRUNCATE is faster when you want to remove everything