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 

Answer 2

MySQL DELETE records with 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:

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.

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

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