How do I do a DELETE in MySQL with NOT IN?

Question

I'm getting this error when running the code:

Error Code: 1093. You can't specify target table 'details' for update in FROM clause

DELETE FROM details WHERE detail NOT IN 
(
    SELECT detail 
    FROM user_details
    JOIN data ON user_details.data_iddata = data.iddata
    JOIN details ON details.iddetails = data.details_iddetails
)

What am I doing wrong here?

Best answer in the dupe is the third one down http://stackoverflow.com/a/9843719/73226 — Martin Smith, Dec 04 '15 at 21:28

score 0 · Answer 1 · answered Dec 04 '15 at 21:25

I don't have a system handy, but I think the problem is that you can reference the same table in a subquery as the main one you are deleting from. I.e. it doesn't like JOIN details ON details.iddetails = data.details_iddetails in the subquery because that's in the main select clause.

One workaround is to create a temp table, insert the records you are interested in into it, and then find your set from a join off of that.

score 0 · Answer 2 · answered Dec 04 '15 at 21:26

You're trying to do a DELETE from a table while you're doing a SELECT from it.

In order to get this to work, make a list from your SELECT query and put its results in the parentheses.

If you aren't doing this in an external script, you should define a view that will give you the desired SELECT output.

How do I do a DELETE in MySQL with NOT IN?

2 Answers2