DELETE — MySQL | LetCodes

DELETE in MySQL

The DELETE statement removes rows from a table. Unlike TRUNCATE, DELETE removes rows one at a time, fires triggers, respects foreign key constraints, and can be rolled back inside a transaction. These properties make it the right choice whenever precision or reversibility matters.

Basic DELETE Syntax

SQL

DELETE FROM table_name
WHERE condition;

SQL

-- Delete a single row by primary key
DELETE FROM employees WHERE employee_id = 42;

-- Delete rows matching a condition
DELETE FROM sessions WHERE expires_at < NOW();

-- Delete with multiple conditions
DELETE FROM orders
WHERE status = 'cancelled'
  AND created_at < DATE_SUB(NOW(), INTERVAL 90 DAY);

DELETE Without WHERE — The Danger

Warning

Omitting the WHERE clause deletes EVERY row in the table. There is no confirmation prompt. Always verify your WHERE condition with a SELECT first before executing DELETE.

SQL

-- DANGEROUS: removes all rows, keeps table structure
DELETE FROM temp_import;

-- Safe pattern: SELECT first to confirm scope
SELECT COUNT(*) FROM temp_import WHERE batch_id = 7;
DELETE FROM temp_import WHERE batch_id = 7;

Safe Delete Mode (sql_safe_updates)

MySQL has a safety guard called sql_safe_updates. When enabled, MySQL refuses DELETE and UPDATE statements that do not filter by an indexed key column, catching many accidental full-table operations.

SQL

-- Enable safe update mode for the current session
SET SESSION sql_safe_updates = 1;

-- This now errors if no indexed key column is in WHERE
DELETE FROM employees;
-- ERROR 1175: You are using safe update mode and you tried to update a table without a WHERE
-- that uses a KEY column

-- Key-based WHERE is allowed
DELETE FROM employees WHERE employee_id = 42;

-- Disable when you intentionally need a full-table delete
SET SESSION sql_safe_updates = 0;

Tip

Enable sql_safe_updates = 1 in your MySQL Workbench preferences or client configuration. It is a cheap safety net that prevents many accidental disasters.

DELETE with ORDER BY and LIMIT (Batch Deletion)

Adding ORDER BY and LIMIT to DELETE restricts how many rows are removed per statement. Combining them in a loop lets you purge large volumes of data without holding a long transaction lock that would block concurrent reads and writes.

SQL

-- Delete in batches of 500 rows at a time
DELETE FROM audit_logs
WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR)
ORDER BY created_at
LIMIT 500;

Bash

#!/bin/bash
# Shell loop: keep deleting until fewer than 500 rows are affected
while true; do
  ROWS=$(mysql mydb -sN -e "
    DELETE FROM audit_logs
    WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR)
    ORDER BY created_at LIMIT 500;
    SELECT ROW_COUNT();
  ")
  echo "Deleted ${ROWS} rows"
  [ "${ROWS}" -lt 500 ] && break
  sleep 0.05
done

Tip

Batch deletes with LIMIT are the safest way to purge large amounts of historical data on a busy production table. They keep individual transactions short, reducing lock contention and replication lag.

Multi-Table DELETE with JOIN

MySQL lets you delete from one or more tables in a single statement by joining them. This is more efficient than two separate DELETE statements because the join is evaluated once.

SQL

-- Delete both the order AND its items in one statement
DELETE o, i
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.status = 'test'
  AND o.created_at < '2024-01-01';

-- Delete only from orders but use a join to filter by customer data
DELETE o
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.is_test_account = 1;

-- Delete users who have never placed an order (LEFT JOIN pattern)
DELETE u
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL
  AND u.created_at < DATE_SUB(NOW(), INTERVAL 6 MONTH);

Note

The tables you want to delete from are listed right after the DELETE keyword. Tables used only for filtering (like in the JOIN condition) are not listed there — only the alias appears after DELETE.

DELETE with a Subquery

SQL

-- Delete orders that contain a discontinued product
DELETE FROM orders
WHERE id IN (
  SELECT order_id FROM order_items WHERE product_id = 99
);

Warning

You cannot DELETE from a table and SELECT from the same table in a direct subquery. MySQL raises an error. Wrap the subquery in a derived table to work around this.

SQL

-- This FAILS in MySQL:
DELETE FROM employees
WHERE department_id IN (
  SELECT department_id FROM employees WHERE manager_id IS NULL
);
-- ERROR 1093: You can't specify target table 'employees' for update in FROM clause

-- Fix: wrap the inner SELECT in a derived table alias
DELETE FROM employees
WHERE department_id IN (
  SELECT department_id FROM (
    SELECT department_id FROM employees WHERE manager_id IS NULL
  ) AS sub
);

Soft Delete Pattern

Rather than removing rows permanently, many applications use a soft delete: a timestamp column marks a row as deleted while keeping the data intact for audits, recovery, or analytics.

SQL

-- Add soft-delete columns to the users table
ALTER TABLE users
  ADD COLUMN is_deleted  BIT(1)     NOT NULL DEFAULT 0,
  ADD COLUMN deleted_at  DATETIME   NULL DEFAULT NULL;

-- Soft delete: mark instead of physically remove
UPDATE users
SET is_deleted = 1,
    deleted_at = NOW()
WHERE user_id = 55;

-- Query only active (non-deleted) users
SELECT * FROM users WHERE is_deleted = 0;
-- Or equivalently:
SELECT * FROM users WHERE deleted_at IS NULL;

-- Restore a soft-deleted user
UPDATE users
SET is_deleted = 0,
    deleted_at = NULL
WHERE user_id = 55;

-- Unique index that still allows multiple deleted users with the same email
-- (partial unique index is not natively supported in MySQL, use a workaround)
CREATE UNIQUE INDEX uq_users_email_active
  ON users (email, is_deleted);
-- This enforces uniqueness per (email, is_deleted) pair

-- Periodic hard-delete of old soft-deleted records
DELETE FROM users
WHERE is_deleted = 1
  AND deleted_at < DATE_SUB(NOW(), INTERVAL 30 DAY)
LIMIT 1000;

Note

Add an index on is_deleted or deleted_at so that queries filtering for active records remain fast as the table grows. A composite index on (is_deleted, created_at) covers common time-range queries on active rows.

Cascading DELETE via Foreign Key

SQL

-- Define a foreign key with ON DELETE CASCADE
CREATE TABLE order_items (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id   INT UNSIGNED NOT NULL,
  product_id INT UNSIGNED NOT NULL,
  quantity   INT          NOT NULL DEFAULT 1,
  CONSTRAINT fk_items_order
    FOREIGN KEY (order_id) REFERENCES orders (id)
    ON DELETE CASCADE
);

-- Deleting the parent order automatically deletes all its items
DELETE FROM orders WHERE id = 100;
-- MySQL also deletes all order_items rows where order_id = 100

Tip

ON DELETE CASCADE is convenient but can silently delete large amounts of related data. Consider ON DELETE RESTRICT (the default) for tables where accidental parent deletion would be catastrophic — it forces you to explicitly clean up children first.

BEFORE DELETE and AFTER DELETE Triggers

SQL

-- Archive row to a history table before deleting
CREATE TRIGGER trg_users_before_delete
BEFORE DELETE ON users
FOR EACH ROW
BEGIN
  INSERT INTO users_deleted_log
    (user_id, email, deleted_by, deleted_at)
  VALUES
    (OLD.id, OLD.email, CURRENT_USER(), NOW());
END;

-- AFTER DELETE: update a summary counter
CREATE TRIGGER trg_users_after_delete
AFTER DELETE ON users
FOR EACH ROW
BEGIN
  UPDATE user_stats
  SET total_users = total_users - 1
  WHERE stat_date = CURDATE();
END;

Warning

TRUNCATE TABLE does NOT fire DELETE triggers. If your logic depends on triggers to archive or audit deleted rows, use DELETE instead of TRUNCATE.

ROW_COUNT() — Checking How Many Rows Were Deleted

SQL

DELETE FROM sessions WHERE expires_at < NOW();

-- ROW_COUNT() returns the number of rows affected by the last DELETE
SELECT ROW_COUNT() AS rows_deleted;

-- In a stored procedure or script:
DELETE FROM notifications WHERE sent = 1 AND sent_at < DATE_SUB(NOW(), INTERVAL 7 DAY);
SET @deleted = ROW_COUNT();
SELECT CONCAT('Deleted ', @deleted, ' rows') AS result;

Archiving Before Deleting

For compliance, auditing, or disaster recovery, insert rows into an archive table before deleting them. This is safer than relying on binary log recovery and gives you a human-readable history.

SQL

-- Step 1: Create an archive table mirroring the source
CREATE TABLE orders_archive LIKE orders;
ALTER TABLE orders_archive ADD COLUMN archived_at DATETIME DEFAULT CURRENT_TIMESTAMP;

-- Step 2: Copy rows to archive before deletion (atomic with a transaction)
START TRANSACTION;

INSERT INTO orders_archive
  SELECT *, NOW() AS archived_at
  FROM orders
  WHERE status = 'cancelled'
    AND created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR);

DELETE FROM orders
WHERE status = 'cancelled'
  AND created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR);

COMMIT;

DELETE vs TRUNCATE vs DROP

Feature	DELETE	TRUNCATE	DROP TABLE
WHERE clause	Yes	No	N/A
Rollback inside transaction	Yes	No	No
Fires DELETE triggers	Yes	No	N/A
Resets AUTO_INCREMENT	No	Yes	N/A
Respects FK constraints	Yes	Fails if FK exists	Fails if FK exists
Removes table structure	No	No	Yes
Speed on full table	Slow (row-by-row)	Very fast (DDL)	Fast

DELETE and InnoDB Locking

When MySQL executes a DELETE it acquires row-level locks on the rows it is about to remove. In READ COMMITTED isolation, locks are released row by row as InnoDB processes them. In REPEATABLE READ (the default), locks are held until the transaction commits or rolls back.

Additionally, InnoDB uses gap locks to prevent phantom reads under REPEATABLE READ: it locks the gap between index records around the deleted rows, preventing concurrent inserts into that range.

SQL

-- See active locks while a DELETE is running
SELECT r.trx_id AS waiting_trx,
       r.trx_mysql_thread_id AS waiting_thread,
       b.trx_id AS blocking_trx,
       b.trx_mysql_thread_id AS blocking_thread,
       l.lock_type, l.lock_mode
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx r  ON r.trx_id = w.requesting_trx_id
JOIN information_schema.innodb_trx b  ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_locks l ON l.lock_id = w.requested_lock_id;

-- Reduce locking: always include the primary key or indexed column in WHERE
-- This takes a precise row lock instead of a large range lock
DELETE FROM sessions WHERE session_id = 'abc123';

Tip

Long-running DELETE transactions hold locks for the entire duration. Use LIMIT batching with brief sleeps between batches to keep lock windows short and allow other transactions to proceed.

Verifying DELETE Safety with EXPLAIN

SQL

-- Always run EXPLAIN on a DELETE before executing on large tables
EXPLAIN DELETE FROM audit_logs
WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY);
-- Look for: type=range (index used) vs type=ALL (full scan — add an index!)

-- Add an index if DELETE is slow
ALTER TABLE audit_logs ADD INDEX idx_created (created_at);

-- Re-verify
EXPLAIN DELETE FROM audit_logs
WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY);
-- type: range   key: idx_created   (much faster)

-- EXPLAIN also shows row estimate — important for batching decisions
-- If rows=5000000, consider LIMIT batching even for a one-time purge

Practical DELETE Patterns by Use Case

Use Case	Pattern	Key Consideration
Delete single record	DELETE WHERE pk = value	Always use the primary key for precision
Expire old data	DELETE WHERE date < cutoff ORDER BY date LIMIT n	Batch with LIMIT to avoid long locks
Remove test data	DELETE WHERE is_test = 1	Verify with SELECT COUNT first
Cascade cleanup	ON DELETE CASCADE on FK	Silent propagation — use RESTRICT if unsure
Audit-sensitive deletion	Soft delete (deleted_at)	Hard delete only after retention period
Cross-table cleanup	Multi-table DELETE with JOIN	Atomic — both tables updated in one statement
Large purge job	Batch loop with LIMIT + sleep	Keep transactions under 1-2 seconds each

Checking the Execution Plan of DELETE

SQL

-- MySQL 8.0+: use EXPLAIN FORMAT=TREE for cleaner output
EXPLAIN FORMAT=TREE
DELETE FROM orders
WHERE status = 'cancelled'
  AND created_at < '2023-01-01';

-- Look for:
-- Filter: (orders.status = 'cancelled') AND (orders.created_at < '2023-01-01')
-- -> Index range scan on orders using idx_status_created
--      (the index is being used -- good)

-- vs a bad plan:
-- -> Table scan on orders  (no index -- add one!)

-- Most useful index for date-range deletes
ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);

DELETE and Replication

In replicated MySQL setups, DELETE behaviour depends on the binary log format:

Statement-based replication (SBR): the DELETE statement is logged once and replayed on each replica. Fast in the binary log, but non-deterministic DELETE (no ORDER BY) can produce different results on replicas if the execution plan differs.
Row-based replication (RBR): every deleted row is logged individually. Safe and deterministic, but deleting millions of rows generates a very large binary log.
Mixed replication: MySQL chooses SBR for safe statements and RBR when the statement is non-deterministic.

For large batch deletes under RBR, keep batch sizes small to prevent the binary log from growing unboundedly between checkpoints.

SQL

-- Check current binary log format
SHOW VARIABLES LIKE 'binlog_format';

-- Row-based: each deleted row generates a Delete_rows event in the binary log
-- A single DELETE of 1M rows = 1M Delete_rows events = potentially gigabytes of binlog

-- Mitigation: batch with LIMIT (each batch is a smaller transaction)
-- Set binlog_row_image = 'MINIMAL' to log only PK values for deletes (MySQL 5.6+)
SHOW VARIABLES LIKE 'binlog_row_image';
-- MINIMAL: only logs the PK (much smaller for wide tables)

GDPR and Data Retention Patterns

SQL

-- GDPR erasure: hard-delete personal data but keep anonymised analytics
-- Step 1: overwrite PII fields with anonymised values
UPDATE users
SET email        = CONCAT('deleted_', id, '@erased.invalid'),
    name         = 'Deleted User',
    phone        = NULL,
    date_of_birth = NULL,
    address      = NULL,
    gdpr_erased_at = NOW()
WHERE id = 55;

-- Step 2: if full deletion is required, delete the row
DELETE FROM users WHERE id = 55;

-- Automated retention: purge users inactive for 3 years
-- (run nightly via an EVENT or external scheduler)
DELETE FROM users
WHERE last_login_at < DATE_SUB(NOW(), INTERVAL 3 YEAR)
  AND is_deleted = 1
ORDER BY last_login_at
LIMIT 500;

DELETE Checklist Before Executing in Production

Run the equivalent SELECT with the same WHERE and confirm row count matches expectation.
Confirm sql_safe_updates is enabled for interactive sessions.
Wrap inside a transaction and run SELECT COUNT(*) inside the transaction before COMMITting.
If deleting more than 10 000 rows, plan for batching with LIMIT.
Check for ON DELETE CASCADE FKs that may silently delete related rows.
Verify with EXPLAIN that the DELETE uses an index (no type:ALL on large tables).
Confirm binary logging is enabled so recovery is possible if something goes wrong.
For soft-deleted data, confirm deleted_at and is_deleted flags are set correctly.

Best Practices

Always write a SELECT with the same WHERE before executing DELETE to verify scope.
Enable sql_safe_updates to prevent accidental full-table deletes.
Use DELETE inside a transaction so you can ROLLBACK if the result is unexpected.
Batch large deletes with LIMIT + ORDER BY to avoid long-running transactions and lock contention.
Prefer soft deletes (deleted_at timestamp) over hard deletes for audit-sensitive data.
Use ON DELETE CASCADE judiciously — explicit child cleanup is safer and more predictable.
Archive rows to a history table before hard-deleting, especially for financial or compliance data.
Index the columns in your WHERE clause to avoid full table scans during DELETE.
Run EXPLAIN on DELETE statements against large tables before executing in production.