Delete from one table with join

Question

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
)

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

Answer 1

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