MySQL 使用连接从一张表中删除
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4154707/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Delete from one table with join
提问by Julian Young
I'm trying to delete records from one database based on a selection criteria of another. We have two tables, emailNotification which stores a list of jobs and emails. Then we have jobs. I want to clear out emailNotifications for jobs that have been closed. I found some earlier examples on Stackoverflow that lead me to this type of syntax (I was previously trying to do the join before the where).
我正在尝试根据另一个数据库的选择标准从一个数据库中删除记录。我们有两个表，emailNotification，用于存储工作和电子邮件列表。然后我们有工作。我想清除已关闭作业的 emailNotifications。我在 Stackoverflow 上发现了一些早期的例子，它们引导我使用这种类型的语法（我以前试图在 where 之前进行连接）。
DELETE FROM emailNotification
WHERE notificationId IN (
SELECT notificationId FROM emailNotification e
LEFT JOIN jobs j ON j.jobId = e.jobId
WHERE j.active = 1
)
I'm getting the error, you can't specify the target table 'emailNotication' for update in the FROM Clause.
我收到错误消息，您无法在 FROM 子句中指定要更新的目标表“emailNotification”。
回答by Naved
I am not sure about your requirement. What I understood from your question is you want to delete all the emails of jobs which are closed. try this one;
我不确定你的要求。我从您的问题中了解到您要删除所有已关闭的工作电子邮件。试试这个；
DELETE e FROM emailNotification e
LEFT JOIN jobs j ON j.jobId = e.jobId
WHERE j.active = 1 AND CURDATE() < j.closeDate
回答by Aman Garg
MySQL DELETE records with JOIN
MySQL 使用 JOIN 删除记录
Delete multiple records from multiple table using Single Query is As below:
使用单一查询从多个表中删除多条记录如下：
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 Patrick McDonald
You could try something like the following:
您可以尝试以下操作：
DELETE FROM emailNotification
WHERE jobId IN (
SELECT jobId FROM jobs j
WHERE j.active = 1
)
回答by Mahmut Gulerce
If the aim is deleting matching rows, like deleting rows in 1st table which have relations in 2nd, to avoid deleting whole 1st table you should put additional "where" condition for 2nd table
如果目的是删除匹配的行，例如删除第一个表中与第二个表有关系的行，为避免删除整个第一个表，您应该为第二个表添加额外的“where”条件
DELETE f FROM firsttable f
LEFT JOIN secondtable s ON f.related_id = .jobId
WHERE s.related_id
