How to Cascade Delete Associated Data in MySQL Using JOIN?

JOIN deletion in MySQL: delete associated data

In relational databases, it is usually necessary to delete master data and related data. In MySQL, you can use the JOIN statement to implement cascading deletes.

Question:

Consider the following table structure:

CREATE TABLE clients (
   client_id INT(11),
   PRIMARY KEY (client_id)
);
CREATE TABLE projects (
   project_id INT(11) UNSIGNED,
   client_id INT(11) UNSIGNED,
   PRIMARY KEY (project_id)
);
CREATE TABLE posts (
   post_id INT(11) UNSIGNED,
   project_id INT(11) UNSIGNED,
   PRIMARY KEY (post_id)
);

When deleting a customer, all associated projects and posts need to be deleted in cascade. However, the following PHP code is not valid:

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

Solution:

To successfully delete posts associated with a deleted customer, you need to specify that you want to delete the entry from the posts table:

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

The modified code will delete the project and its associated posts when a customer is deleted.

The above is the detailed content of How to Cascade Delete Associated Data in MySQL Using JOIN?. For more information, please follow other related articles on the PHP Chinese website!