MySQL Delete From Multiple Tables

You can mysql delete from multiple tables with one query – You can define foreign key constraints on the tables with ON DELETE CASCADE option.

Contents

ON DELETE CASCADE option
MySQL DELETE JOIN with INNER JOIN
MySQL DELETE JOIN with LEFT JOIN
Drop multiple MySQL tables
Multiple-Table Deletes and Updates

Use a JOIN in the DELETE statement.

DELETE p, prfl
      FROM profiles p
      JOIN profiles_information prfl ON prfl.id = p.profile_id
     WHERE p.member > :member
       AND p.profile_id = :profile_id

Alternatively : mysql delete from multiple tables

DELETE prfl
      FROM profiles_information prfl
      JOIN profiles p ON prfl.id = p.profile_id
 WHERE p.member > :member
   AND p.profile_id = :profile_id

to delete only from profiles_information

Also Read: Mysql Insert If Not Exists Else Update

MySQL DELETE JOIN with INNER JOIN using mysql delete from multiple tables

DROP TABLE IF EXISTS table_first, table_second;

CREATE TABLE table_first (
    id INT PRIMARY KEY AUTO_INCREMENT
);

CREATE TABLE table_second (
    id VARCHAR(20) PRIMARY KEY,
    ref INT NOT NULL
);

INSERT INTO table_first VALUES (1),(2),(3);

INSERT INTO table_second(id,ref) VALUES('A',1),('B',2),('C',3);

Delete multiple records with help of join query.

DELETE table_first,table_second FROM table_first
        INNER JOIN
    table_second ON table_second.ref = table_first.id 
WHERE
    table_first.id = 1;

DELETE members, members, visior FROM members INNER JOIN members INNER JOIN WHERE members.id=members.member_id AND members.member_id= visior.member_id

MySQL DELETE JOIN with LEFT JOIN using mysql delete from multiple tables

DELETE members 
FROM members
        LEFT JOIN
    upcomming ON members.memberNumber = upcomming.memberNumber 
WHERE
    memberNumber IS NULL;

mysql delete from multiple tables : Left join is also one of easiest way to delete all together.

DELETE members, members, visior FROM members LEFT JOIN members ON members.member_id=members.id WHERE members.member_id=visior.member_id;

Drop multiple MySQL tables

DROP TABLE movies, upcomming, transactions;

https://dev.mysql.com/doc/refman/8.0/en/delete.html

Multiple Delete Queries :

mysql delete from multiple tables multiple query

begin transaction

DELETE FROM members WHERE id=2

DELETE FROM members WHERE member_id=2

DELETE FROM visiors WHERE member_id=2

commit transaction

Multiple-Table Deletes and Updates

DELETE FROM t WHERE id > 200;

DELETE table_first FROM table_first INNER JOIN table_second ON table_first.id = table_second.id;

DELETE table_first, table_second FROM table_first INNER JOIN table_second ON table_first.id = table_second.id;

SELECT table_first.* FROM table_first LEFT JOIN table_second ON table_first.id = table_second.id WHERE table_second.id IS NULL;

DELETE table_first FROM table_first LEFT JOIN table_second ON table_first.id = table_second.id WHERE table_second.id IS NULL;

DELETE FROM table_first USING table_first INNER JOIN table_second ON table_first.id = table_second.id;
DELETE FROM table_first, table_second USING table_first INNER JOIN table_second ON table_first.id = table_second.id;
DELETE FROM table_first USING table_first LEFT JOIN table_second ON table_first.id = table_second.id WHERE table_second.id IS NULL;

UPDATE marks, grade_exam SET marks.marks = marks.marks + 1
WHERE marks.exam_id = grade_exam.exam_id
AND grade_exam.date = '2022-09-23' AND grade_exam.category = 'Q';

UPDATE marks SET marks = marks + 1
WHERE exam_id = (SELECT exam_id FROM grade_exam
WHERE date = '2022-09-23' AND category = 'Q');

UPDATE table_first, table_second SET table_second.a = table_first.a WHERE table_second.id = table_first.id;

I hope you get an idea about mysql delete from multiple tables.
I would like to have feedback on my infinityknow.com blog.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.

MySQL delete from multiple tables – MySQL DELETE JOIN: Deleting Data from Multiple Tables – Best 5 Example