Delete with Join in MySQL

Question

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.

Answer 1

mysql> INSERT INTO tb1 VALUES(1,1),(2,2),(3,3),(6,60),(7,70),(8,80);

mysql> INSERT INTO tb2 VALUES(1,1),(2,2),(3,3),(4,40),(5,50),(9,90);

DELETE records FROM one table :

mysql> DELETE tb1 FROM tb1,tb2 WHERE tb1.id= tb2.id;

DELETE RECORDS FROM both tables:

mysql> DELETE tb2,tb1 FROM tb2 JOIN tb1 USING(id);

Answer 2

If join does not work for you you may try this solution. It is for deleting orphan records from t1 when not using foreign keys + specific where condition. I.e. it deletes records from table1, that have empty field "code" and that do not have records in table2, matching by field "name".

delete table1 from table1 t1 
    where  t1.code = '' 
    and 0=(select count(t2.name) from table2 t2 where t2.name=t1.name);

Answer 3

Single Table Delete:

In order to delete entries from posts table:

DELETE ps 
FROM clients C 
INNER JOIN projects pj ON C.client_id = pj.client_id
INNER JOIN posts ps ON pj.project_id = ps.project_id
WHERE C.client_id = :client_id;

In order to delete entries from projects table:

DELETE pj 
FROM clients C 
INNER JOIN projects pj ON C.client_id = pj.client_id
INNER JOIN posts ps ON pj.project_id = ps.project_id
WHERE C.client_id = :client_id;

In order to delete entries from clients table:

DELETE C
FROM clients C 
INNER JOIN projects pj ON C.client_id = pj.client_id
INNER JOIN posts ps ON pj.project_id = ps.project_id
WHERE C.client_id = :client_id;

---

Multiple Tables Delete:

In order to delete entries from multiple tables out of the joined results you need to specify the table names after DELETE as comma separated list:

Suppose you want to delete entries from all the three tables (posts,projects,clients) for a particular client :

DELETE C,pj,ps 
FROM clients C 
INNER JOIN projects pj ON C.client_id = pj.client_id
INNER JOIN posts ps ON pj.project_id = ps.project_id
WHERE C.client_id = :client_id

Answer 4

Another method of deleting using a sub select that is better than using IN would be WHERE EXISTS

DELETE  FROM posts
WHERE   EXISTS ( SELECT  1 
                 FROM    projects
                 WHERE   projects.client_id = posts.client_id);

One reason to use this instead of the join is that a DELETE with JOIN forbids the use of LIMIT. If you wish to delete in blocks so as not to produce full table locks, you can add LIMIT use this DELETE WHERE EXISTS method.

Answer 5

Try like below:

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

Answer 6

Try this,

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