Practice Now
MySQL
SQL
database management
data manipulation
delete operation

How to delete from multiple tables in MySQL?

System Design practice on Codemia

Work through 120+ system design problems with detailed solutions, from rate limiters to multi-region storage.

Practice system design
Databases

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

ApproachProsCons
DELETE JOINSingle query, explicit controlComplex syntax, must list all tables
ON DELETE CASCADEAutomatic, clean schemaHidden behavior, can cascade unexpectedly
TransactionClear ordering, safeMultiple round trips, more verbose
SubqueriesFlexible filteringCannot 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
Related reading
Course
Beginner
27 lessons
10 hours
System Design Fundamentals

Build a strong foundation in designing scalable, reliable distributed systems.

View the course
Track what you have practised

A free account saves your progress, solutions and study plan across every problem on Codemia.

System Design practice on Codemia

Work through 120+ system design problems with detailed solutions, from rate limiters to multi-region storage.

Practice system design
Codemia
© 2026 Codemia
Resources
Legal
Social
All Rights Reserved.