Delete with Join in MySQL

Abi_Noda_74980 0
Viewed 372098

Here is the script to create my tables: 

CREATE TABLE clients (
   client_i 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)
);

In my PHP code, when deleting a client, I want to delete all projects posts:

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

The posts table does not have a foreign key client_id, only project_id. I want to delete the posts in projects that have the passed client_id.

This is not working right now because no posts are deleted.

mysql
14 Answers

I'm more used to the subquery solution to this, but I have not tried it in MySQL:

DELETE  FROM posts
WHERE   project_id IN (
            SELECT  project_id
            FROM    projects
            WHERE   client_id = :client_id
        );
user_7260

One solution is to use subquery

DELETE FROM posts WHERE post_id in (SELECT post_id FROM posts p
INNER JOIN projects prj ON p.project_id = prj.project_id 
INNER JOIN clients c on prj.client_id = c.client_id WHERE c.client_id = :client_id 
);

The subquery returns the ID that need to be deleted; all three tables are connected using joins and only those records are deleted that meets the filter condition (in yours case i.e. client_id in the where clause).

user_34887470
Related