Home >Database >Mysql Tutorial >How to Cascade Delete Associated Data in MySQL Using JOIN?

How to Cascade Delete Associated Data in MySQL Using JOIN?

Patricia Arquette
Patricia ArquetteOriginal
2025-01-20 16:56:09848browse

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:

<code class="language-sql">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)
);</code>

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

<code class="language-sql">DELETE
FROM posts
INNER JOIN projects ON projects.project_id = posts.project_id
WHERE projects.client_id = :client_id;</code>

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:

<code class="language-sql">DELETE posts
FROM posts
INNER JOIN projects ON projects.project_id = posts.project_id
WHERE projects.client_id = :client_id;</code>

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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn