Delete with Join in MySQL
Or the same thing, with a slightly different (IMO friendlier) syntax:
DELETE FROM posts
USING posts, projects
WHERE projects.project_id = posts.project_id AND projects.client_id = :client_id;
BTW, with mysql using joins is almost always a way faster than subqueries...
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.
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
You can also use ALIAS like this it works just used it on my database! t is the table need deleting from!
DELETE t FROM posts t
INNER JOIN projects p ON t.project_id = p.project_id
AND t.client_id = p.client_id