通过联接从一个表中删除 [英] Delete from one table with join

问题描述

我正在尝试根据另一个数据库的选择标准从一个数据库中删除记录.我们有两个表emailNotification，用于存储作业和电子邮件列表.然后我们有工作.我想清除已关闭作业的emailNotifications.我在Stackoverflow上找到了一些较早的示例，这些示例将我带到了这种语法类型(我以前曾尝试在where之前进行连接).

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

DELETE FROM emailNotification
WHERE notificationId IN (
 SELECT notificationId FROM emailNotification e
 LEFT JOIN jobs j ON j.jobId = e.jobId
WHERE j.active = 1
)

我遇到了错误，您无法在FROM子句中指定要更新的目标表'emailNotication'.

I'm getting the error, you can't specify the target table 'emailNotication' for update in the FROM Clause.

推荐答案

我不确定您的要求. 我从您的问题中了解到，您想删除所有已关闭的工作电子邮件. 试试这个；

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

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