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.

From http://dev.mysql.com/doc/refman/5.0/en/delete.html: Currently, you cannot delete from a table and select from the same table in a subquery, see my answer below — Patrick McDonald, Nov 11 '10 at 13:36

score 101 · Accepted Answer · edited Nov 11 '10 at 13:29

101

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

edited Nov 11 '10 at 13:29

knittl

246,190
53
318
364

answered Nov 11 '10 at 13:28

Naved

4,020
4
31
45

1

That's pretty much it yes, the emailnotification table stores a user and a job, it ensures users are not emailed more than once for a given job (think job alerts on a recruitment website). I'll give this a try now. – Julian Young Nov 11 '10 at 13:32
10

This is a great answer. What is subtle here is that you have to include the table alias after the DELETE keyword. I've never done that before, but I guess it is necessary when you have a JOIN in a delete statement. – Nate Dec 04 '14 at 01:13
3

You can also delete the matching rows from the jobs table "j" when you write something like `DELETE e, j FROM ...` See [here](http://www.mysqltutorial.org/mysql-delete-join/) – beipawel Sep 21 '15 at 20:18
Also it is not strictly necessary to use aliases, in case anyone was thinking otherwise. You could just use the full table names and no aliases if that is your preference. – still_dreaming_1 Dec 08 '20 at 18:17

Aman Garg · Answer 2 · 2015-08-05T06:41:35.410

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.

From source code I see that was published on 2013-05-07 so definitely @Aman plagiarised :P — Umair Ayub, May 08 '19 at 10:04

score 4 · Answer 3 · answered Nov 11 '10 at 13:31

4

You could try something like the following:

DELETE FROM emailNotification
WHERE jobId IN (
 SELECT jobId FROM jobs j
 WHERE j.active = 1
)

answered Nov 11 '10 at 13:31

Patrick McDonald

64,141
14
108
120

This doesn't mention the JOIN but I like this answer because it points to the building blocks needed for the answer. The aspect of a join being used is less important, as that is just part of the sub query on lines 3 and 4 in this example code - the sub query could be JOINs or simply have a where clause across multiple tables. – benjaminhull Aug 29 '19 at 12:15

score 2 · Answer 4 · answered Oct 21 '18 at 10:14

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

DELETE f FROM firsttable f 
LEFT JOIN secondtable s ON f.related_id = .jobId 
WHERE s.related_id

Delete from one table with join

4 Answers4

Linked

Related