在MySQL中加入联接删除 [英] Delete with Join in MySQL

问题描述

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

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

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

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;

posts表没有外键client_id，只有project_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.

推荐答案

您只需要指定要从posts表中删除的条目即可:

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

有关更多信息，请参见此替代答案

For more information you can see this alternative answer

这篇关于在MySQL中加入联接删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！