How to Delete Records Across Multiple Tables in MySQL Using SQL Joins?

Delete records using SQL JOIN in MySQL

Overview

This article explores the use of SQL JOIN to delete records from multiple tables in MySQL. Specifically, we studied a scenario where we wanted to delete all posts related to a client when it was removed from the system.

Problem Statement

Suppose we have three tables: clients, projects and posts. The projects table has a foreign key client_id that refers to the clients table, and the posts table has a foreign key project_id that refers to the projects table. When a specific client is deleted, we want all posts associated with that client to be deleted.

Solution

For this, we use SQL JOIN to delete posts indirectly. Here is the updated code:

<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>

In this query, we use an INNER JOIN to specify that we want to delete posts that have corresponding projects in the projects table, where the client_id matches the client we want to delete.

Alternative methods

Another alternative to deleting posts is to use a foreign key cascade delete. This involves adding the following constraints to the posts table:

<code class="language-sql">ALTER TABLE posts ADD FOREIGN KEY (project_id) REFERENCES projects(project_id) ON DELETE CASCADE;</code>

With this constraint, when you delete an item, all posts associated with that item are automatically deleted as well.

The above is the detailed content of How to Delete Records Across Multiple Tables in MySQL Using SQL Joins?. For more information, please follow other related articles on the PHP Chinese website!