How to Delete Records from a Child Table Using a JOIN in MySQL?

Use JOIN to delete subtable records in MySQL

In relational databases such as MySQL, deleting records may involve multiple tables and complex relationships. One scenario is when you need to delete records from a child table based on a condition involving the parent table.

Scene:

Consider the following database structure:

• clients table, client_id as primary key
• projects table, project_id as primary key, client_id as foreign key
• posts table, post_id as primary key, project_id as foreign key

Target:

Suppose you wish to delete all posts associated with a given client by deleting the client itself. However, the posts table does not reference client_id directly. It only references project_id.

Initial attempt (invalid):

DELETE 
FROM posts
INNER JOIN projects ON projects.project_id = posts.project_id
WHERE projects.client_id = :client_id;

Solution:

In order to correct the problem and successfully delete the post, you need to specify the table name (posts) in the DELETE statement:

DELETE posts
FROM posts
INNER JOIN projects ON projects.project_id = posts.project_id
WHERE projects.client_id = :client_id;

This modified query explicitly targets the posts table for deletion based on the join conditions.

The above is the detailed content of How to Delete Records from a Child Table Using a JOIN in MySQL?. For more information, please follow other related articles on the PHP Chinese website!