在MySQL中使用Join删除

下面是创建我的表的脚本:

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

在我的PHP代码中，当删除客户端时，我想删除所有项目的帖子:

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

posts表没有外键client_id，只有project_id。我想删除具有传递client_id的项目中的帖子。

这是不工作的，因为没有帖子被删除。

当前回答

一种解决方案是使用子查询

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

子查询返回需要删除的ID;所有三个表都使用连接连接，只有那些符合过滤条件的记录被删除(在你的例子中，即where子句中的client_id)。

2020-10-27 10:14:23

其他回答

或者是同样的东西，只是语法略有不同(IMO更友好):

DELETE FROM posts 
USING posts, projects 
WHERE projects.project_id = posts.project_id AND projects.client_id = :client_id;

顺便说一句，在mysql中使用连接几乎总是比子查询更快…

2011-09-01 04:14:09

你只需要指定你想要从posts表中删除条目:

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

编辑:要了解更多信息，您可以查看这个替代答案

2010-11-16 09:51:36

试试这个,

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

2016-04-04 09:18:58

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

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等的贡献。

2015-03-23 07:18:09

单表删除:

为了从posts表中删除条目:

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;

为了从项目表中删除条目:

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;

为了从clients表中删除条目:

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;

多表删除:

为了从连接的结果中删除多个表的条目，你需要指定delete后的表名为逗号分隔的列表:

假设你想要删除一个特定客户端的所有三个表(posts,projects,clients)中的条目:

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

2016-08-14 04:12:47

在MySQL中使用Join删除

推荐文章

最新文章

标签