在 MySQL 中使用 Join 删除

Delete with Join in MySQL

提问by GeekJock

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:

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

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.

post 表没有外键client_id，只有project_id. 我想删除项目中已通过client_id.

This is not working right now because no posts are deleted.

这现在不起作用，因为没有删除任何帖子。

回答by Yehosef

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

您只需要指定要从posts表中删除条目：

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

编辑：有关更多信息，您可以查看此替代答案

回答by Pacerier

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_name1and table_name2are the same table, so this will work:

在这种情况下，table_name1并且table_name2是同一个表，所以这将起作用：

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 byand limitdon't work for multi-table deletes.

请注意，order by并且limit不适用于多表删除。

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.}

^{来自 Carpetsmoker 等的贡献。}

回答by ivanhoe

Or the same thing, with a slightly different (IMO friendlier) syntax:

或者同样的事情，语法略有不同（IMO 友好）：

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...

顺便说一句，使用连接的mysql几乎总是比子查询快...

回答by Property Spain

You can also use ALIAS like this it works just used it on my database! t is the table need deleting from!

你也可以像这样使用 ALIAS 它只是在我的数据库上使用它！t 是需要删除的表！

DELETE t FROM posts t
INNER JOIN projects p ON t.project_id = p.project_id
AND t.client_id = p.client_id

回答by yukondude

I'm more used to the subquery solution to this, but I have not tried it in MySQL:

我更习惯于子查询解决方案，但我还没有在MySQL中尝试过：

DELETE  FROM posts
WHERE   project_id IN (
            SELECT  project_id
            FROM    projects
            WHERE   client_id = :client_id
        );

回答by 1000111

Single Table Delete:

单表删除：

In order to delete entries from poststable:

为了从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;

In order to delete entries from projectstable:

为了从projects表中删除条目：

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;

In order to delete entries from clientstable:

为了从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;

---

---

Multiple Tables Delete:

多表删除：

In order to delete entries from multiple tables out of the joined results you need to specify the table names after DELETEas comma separated list:

为了从连接结果中删除多个表中的条目，您需要在DELETE以逗号分隔的列表后指定表名：

Suppose you want to delete entries from all the three tables (posts,projects,clients) for a particular client :

假设您要从特定客户端的所有三个表 ( 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

回答by Aman Garg

MySQL DELETE records with JOIN

MySQL 使用 JOIN 删除记录

You generally use INNER JOIN in the SELECT statement to select records from a table that have corresponding records in other tables. We can also use the INNER JOIN clause with the DELETE statement to delete records from a table and also the corresponding records in other tables e.g., to delete records from both T1 and T2 tables that meet a particular condition, you use the following statement:

您通常在 SELECT 语句中使用 INNER JOIN 从一个表中选择在其他表中具有相应记录的记录。我们还可以使用带有 DELETE 语句的 INNER JOIN 子句从表中删除记录以及其他表中的相应记录，例如，从 T1 和 T2 表中删除满足特定条件的记录，您可以使用以下语句：

DELETE T1, T2
FROM T1
INNER JOIN T2 ON T1.key = T2.key
WHERE condition

Notice that you put table names T1 and T2 between DELETE and FROM. If you omit the T1 table, the DELETE statement only deletes records in the T2 table, and if you omit the T2 table, only records in the T1 table are deleted.

请注意，您将表名 T1 和 T2 放在 DELETE 和 FROM 之间。如果省略T1表，则DELETE语句只删除T2表中的记录，如果省略T2表，则只删除T1表中的记录。

The join condition T1.key = T2.key specifies the corresponding records in the T2 table that need be deleted.

连接条件 T1.key = T2.key 指定了 T2 表中需要删除的对应记录。

The condition in the WHERE clause specifies which records in the T1 and T2 that need to be deleted.

WHERE 子句中的条件指定需要删除 T1 和 T2 中的哪些记录。

回答by Sangeetha Narayana Moorthy

Try like below:

尝试如下：

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

回答by zloctb

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

DELETE records FROM one table :

从一张表中删除记录：

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

DELETE RECORDS FROM both tables:

从两个表中删除记录：

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

回答by Jim Clouse

Another method of deleting using a sub select that is better than using INwould be WHEREEXISTS

使用比使用更好的子选择删除的另一种方法IN是WHEREEXISTS

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

One reason to use this instead of the join is that a DELETEwith JOINforbids the use of LIMIT. If you wish to delete in blocks so as not to produce full table locks, you can add LIMITuse this DELETE WHERE EXISTSmethod.

使用 this 而不是 join 的一个原因是DELETEwithJOIN禁止使用LIMIT. 如果您希望在块中删除以不产生全表锁，则可以LIMIT使用此DELETE WHERE EXISTS方法添加。