How to delete from multiple tables in MySQL?

MySQL

SQL

database management

data manipulation

delete operation

How to delete from multiple tables in MySQL?

Master System Design with Codemia

Enhance your system design skills with over 120 practice problems, detailed solutions, and hands-on exercises.

Start Practicing Learn More

Introduction

Deleting from multiple tables in MySQL is necessary when managing related data with foreign key relationships. When you delete a parent record, you typically need to delete associated child records as well. MySQL provides two main approaches: multi-table DELETE with JOIN and foreign key constraints with ON DELETE CASCADE.

Method 1: DELETE with JOIN

The multi-table DELETE statement removes rows from multiple tables in a single query:

Syntax

sql

1DELETE t1, t2
2FROM table1 t1
3JOIN table2 t2 ON t1.id = t2.table1_id
4WHERE t1.some_column = 'some_value';

Example: Delete a User and Their Orders

sql

1-- Delete from both users and orders where user_id = 5
2DELETE users, orders
3FROM users
4JOIN orders ON users.id = orders.user_id
5WHERE users.id = 5;

Delete from One Table Based on Another

sql

1-- Delete orders for inactive users (but keep the users)
2DELETE orders
3FROM orders
4JOIN users ON orders.user_id = users.id
5WHERE users.status = 'inactive';

Delete from Three Tables

sql

1-- Delete user, their orders, and order items
2DELETE users, orders, order_items
3FROM users
4JOIN orders ON users.id = orders.user_id
5JOIN order_items ON orders.id = order_items.order_id
6WHERE users.id = 5;

Method 2: ON DELETE CASCADE

Define the cascade behavior in the foreign key constraint so deletions propagate automatically:

sql

1CREATE TABLE users (
2    id INT PRIMARY KEY AUTO_INCREMENT,
3    name VARCHAR(100),
4    email VARCHAR(255)
5);
6
7CREATE TABLE orders (
8    id INT PRIMARY KEY AUTO_INCREMENT,
9    user_id INT,
10    total DECIMAL(10,2),
11    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
12);
13
14CREATE TABLE order_items (
15    id INT PRIMARY KEY AUTO_INCREMENT,
16    order_id INT,
17    product_name VARCHAR(100),
18    quantity INT,
19    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
20);
21
22-- Now a single DELETE cascades through all related tables
23DELETE FROM users WHERE id = 5;
24-- Automatically deletes all orders for user 5
25-- And all order_items for those orders

Adding CASCADE to Existing Tables

sql

1-- Drop existing foreign key
2ALTER TABLE orders DROP FOREIGN KEY orders_ibfk_1;
3
4-- Add it back with CASCADE
5ALTER TABLE orders
6ADD CONSTRAINT fk_orders_user
7FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

Method 3: Multiple DELETE Statements in a Transaction

When you cannot use JOINs or CASCADE:

sql

1START TRANSACTION;
2
3-- Delete child records first (bottom-up)
4DELETE FROM order_items
5WHERE order_id IN (SELECT id FROM orders WHERE user_id = 5);
6
7DELETE FROM orders WHERE user_id = 5;
8
9DELETE FROM users WHERE id = 5;
10
11COMMIT;

Method 4: Using Subqueries

sql

1-- Delete orders for users in a specific city
2DELETE FROM orders
3WHERE user_id IN (
4    SELECT id FROM users WHERE city = 'NYC'
5);
6
7-- Then delete the users
8DELETE FROM users WHERE city = 'NYC';

Note: MySQL does not allow deleting from a table and selecting from it in the same query. Use a subquery workaround:

sql

1-- This fails in MySQL
2DELETE FROM users WHERE id IN (SELECT user_id FROM users WHERE status = 'inactive');
3
4-- Workaround: wrap in a derived table
5DELETE FROM users WHERE id IN (
6    SELECT id FROM (SELECT id FROM users WHERE status = 'inactive') AS tmp
7);

Comparison of Approaches

Approach	Pros	Cons
DELETE JOIN	Single query, explicit control	Complex syntax, must list all tables
ON DELETE CASCADE	Automatic, clean schema	Hidden behavior, can cascade unexpectedly
Transaction	Clear ordering, safe	Multiple round trips, more verbose
Subqueries	Flexible filtering	Cannot self-reference in MySQL, slower

Safe Deletion Pattern

Always preview before deleting:

sql

1-- Preview: see what will be deleted
2SELECT users.*, orders.*
3FROM users
4JOIN orders ON users.id = orders.user_id
5WHERE users.id = 5;
6
7-- Then delete
8DELETE users, orders
9FROM users
10JOIN orders ON users.id = orders.user_id
11WHERE users.id = 5;

Common Pitfalls

Foreign key violations: Without CASCADE, deleting a parent row with existing child references fails: Cannot delete or update a parent row: a foreign key constraint fails. Delete children first or use CASCADE.
Accidental cascade: CASCADE can propagate deletions further than expected. A single DELETE FROM users could wipe out orders, order_items, payments, and reviews if all have CASCADE constraints. Audit your schema carefully.
Missing WHERE clause: DELETE users, orders FROM users JOIN orders ON ... without a WHERE clause deletes all matching rows across both tables. Always include a WHERE clause.
Performance: Multi-table deletes on large tables can lock rows for extended periods. Consider batching: DELETE FROM orders WHERE user_id = 5 LIMIT 1000 in a loop.
Backup: Always back up or use transactions with ROLLBACK capability before mass deletions. DELETE is not easily reversible without a backup.

Summary

Use DELETE t1, t2 FROM t1 JOIN t2 ON ... for explicit multi-table deletion
Use ON DELETE CASCADE for automatic propagation through foreign key relationships
Use transactions with multiple DELETE statements when order matters
Always preview with SELECT before executing DELETE on production data
Be cautious with CASCADE — audit your foreign key chain to understand propagation depth