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.