试试这个,

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

试试下面的方法:

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

使用子选择的另一种删除方法是WHERE EXISTS，这种方法比使用IN更好

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

使用这个而不是连接的一个原因是带有join的DELETE禁止使用LIMIT。如果希望以块的形式进行删除，从而不产生完整的表锁，则可以使用delete WHERE EXISTS方法添加LIMIT。

由于您正在选择多个表，要删除的表不再是明确的。您需要选择:

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

在这种情况下，table_name1和table_name2是同一个表，所以这样可以工作:

DELETE projects FROM posts INNER JOIN [...]

你甚至可以删除这两个表，如果你想:

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

注意，order by和limit不适用于多表删除。

还要注意，如果你为一个表声明了别名，那么在引用该表时必须使用别名:

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

来自Carpetsmoker等的贡献。

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

从一个表中删除记录:

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

删除记录从两个表:

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

如果join不适合你，你可以尝试这个解决方案。它用于在不使用外键+特定的where条件时从t1中删除孤立记录。也就是说，它从table1中删除有空字段“code”而在table2中没有记录的记录，通过字段“name”匹配。

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