Can a Single MySQL Query Delete Data from Multiple Tables Simultaneously?

Deleting Multiple Tables in MySQL with a Single Query

Having multiple tables storing user-related information can pose challenges when trying to efficiently remove user data. The question arises: "Is it possible to delete data from multiple tables simultaneously with a single query in SQL?"

The Approach:

The provided code implements a straightforward approach:

DELETE FROM table1 WHERE user_id='$user_id';
DELETE FROM table2 WHERE user_id='$user_id';
DELETE FROM table3 WHERE user_id='$user_id';
DELETE FROM table4 WHERE user_id='$user_id';

This approach involves executing separate DELETE statements for each table, cascading the deletion process. However, it may not be the most efficient or flexible option.

A Pivotal Revelation:

Remarkably, MySQL offers a more advanced technique to achieve this task. According to the official documentation, it is indeed possible to delete rows from multiple tables in a single query. By utilizing the JOIN syntax, we can establish relationships between tables based on a common condition (in this case, the user_id field).

The Revised Code:

The manual provides an illustrative example:

DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;

In our scenario, we can adapt this approach as follows:

DELETE user_info.*
FROM user_info
INNER JOIN table1 ON user_info.user_id = table1.user_id
INNER JOIN table2 ON user_info.user_id = table2.user_id
INNER JOIN table3 ON user_info.user_id = table3.user_id
INNER JOIN table4 ON user_info.user_id = table4.user_id
WHERE user_info.user_id = '$user_id';

By using this technique, we delete rows from all four tables related to the user specified by the $user_id in a single query, effectively mimicking the previous approach but with improved efficiency.

The above is the detailed content of Can a Single MySQL Query Delete Data from Multiple Tables Simultaneously?. For more information, please follow other related articles on the PHP Chinese website!