MySQL DELETE Remove Rows

DELETE FROM ... WHERE

Choose hard deletion only when retention and recovery rules permit it; otherwise model a reversible archival or soft-delete state.

The DELETE statement removes rows from a table. Always use a WHERE clause - omitting it deletes every row in the table. Run the equivalent SELECT first to confirm which rows will be removed.

Basic DELETE and DELETE with LIMIT

-- Delete a specific row by primary key
DELETE FROM customers WHERE customer_id = 5;

-- Delete rows matching a condition
DELETE FROM orders WHERE status = 'cancelled';

-- Delete with multiple conditions
DELETE FROM products
WHERE stock = 0 AND category = 'Electronics';

-- Delete with LIMIT (remove at most N rows at a time - useful for large tables)
DELETE FROM orders
WHERE status = 'cancelled'
LIMIT 1000;

-- Preview rows before deleting (always a good practice)
SELECT * FROM orders WHERE status = 'cancelled' LIMIT 10;

-- Delete all rows (use TRUNCATE instead for better performance)
DELETE FROM order_items;

DELETE with JOIN

MySQL supports deleting rows from one or more tables using a JOIN. This is useful when you need to delete rows based on related data in another table.

DELETE with JOIN and Subquery

-- DELETE with JOIN: delete order_items for cancelled orders
DELETE oi
FROM order_items oi
INNER JOIN orders o ON oi.order_id = o.order_id
WHERE o.status = 'cancelled';

-- DELETE from multiple tables in one statement
DELETE o, oi
FROM orders o
INNER JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.status = 'cancelled' AND o.ordered_at < '2023-01-01';

-- DELETE with subquery
DELETE FROM customers
WHERE customer_id NOT IN (
    SELECT DISTINCT customer_id FROM orders
);

TRUNCATE vs DELETE

TRUNCATE TABLE removes all rows much faster than DELETE because it drops and recreates the table internally, bypassing row-by-row logging. However, it cannot be rolled back in most cases and resets the AUTO_INCREMENT counter.

TRUNCATE vs DELETE and Soft Delete Pattern

-- TRUNCATE: fast, resets AUTO_INCREMENT, cannot be rolled back
TRUNCATE table order_items;

-- DELETE: slower, logged, can be rolled back inside a transaction
DELETE FROM order_items;

-- Soft Delete Pattern: instead of deleting, mark rows as deleted
-- Add a deleted_at column to the table
ALTER table customers
    ADD COLUMN deleted_at DATETIME DEFAULT NULL;

-- "Delete" a customer (soft delete)
UPDATE customers
SET deleted_at = NOW()
WHERE customer_id = 5;

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

-- Restore a soft-deleted customer
UPDATE customers
SET deleted_at = NULL
WHERE customer_id = 5;

-- Permanently purge soft-deleted records older than 90 days
DELETE FROM customers
WHERE deleted_at IS NOT NULL
  AND deleted_at < NOW() - INTERVAL 90 DAY;

Deleting MySQL data without removing the wrong rows

DELETE removes rows from a table. Because deleted rows may be difficult or impossible to recover, the WHERE clause is the most important part of the statement. Many applications use soft deletes, such as setting deleted_at, when business records should disappear from screens but remain available for audit or recovery.

Foreign keys can also affect DELETE behavior. A parent row may be blocked if child rows still reference it, or related rows may be deleted automatically if cascade rules exist. Before deleting, understand relationships, preview the matching rows, and decide whether hard delete or soft delete is appropriate.

Run SELECT with the same WHERE condition first.
Use transactions for important deletes.
Understand foreign key restrictions and cascades.
Consider soft delete for user, order, and audit-sensitive data.

Delete expired sessions safely

START TRANSACTION;

DELETE FROM sessions
WHERE expires_at < NOW();

COMMIT;

Before you move on