A single DELETE statement on multiple related tables which the child table have an ON DELETE CASCADE referential action for the foreign key.
This tutorial introduces to you a more flexible way to delete data from multiple tables using INNER JOINor LEFT JOIN clause with the DELETE statement.
MySQL DELETE JOIN with INNER JOIN
MySQL also allows you to use the INNER JOIN clause in the DELETE statement to delete rows from a table and the matching rows in another table.
For example, to delete rows from both T1 and T2 tables that meet a specified condition, you use the following statement:
Notice that you put table names T1 and T2 between the DELETE and FROM keywords. If you omit T1 table, the DELETE statement only deletes rows in T2 table. Similarly, if you omitT2 table, the DELETE statement will delete only rows in T1 table.
The expression T1.key = T2.key specifies the condition for matching rows between T1 andT2 tables that will be deleted.
The condition in the WHERE clause determine rows in the T1 and T2 that will be deleted.
MySQL DELETE JOIN with INNER JOIN example
Suppose, we have two tables t1 and t2 with the following structures and data: