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

You just need to specify that you want to delete the entries from the posts table:

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

EDIT: For more information you can see this alternative answer

Answer 2

Since you are selecting multiple tables, The table to delete from is no longer unambiguous. You need to select:

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

In this case, table_name1 and table_name2 are the same table, so this will work:

DELETE projects FROM posts INNER JOIN [...] 

You can even delete from both tables if you wanted to:

DELETE posts, projects FROM posts INNER JOIN [...] 

Note that order by and limit don't work for multi-table deletes.

Also be aware that if you declare an alias for a table, you must use the alias when referring to the table:

DELETE p FROM posts as p INNER JOIN [...] 

---

^{Contributions from Carpetsmoker and etc.}