MySQL - How to delete from table when nested select uses that table?

Question

I'm trying to do something like this:

DELETE FROM table_1
WHERE  table_1.id IN (SELECT table_1.id
                      FROM   table_1,
                             table_2,
                             table_3
                      WHERE  table_1.id = table_2.table_1_id
                      AND    table_2.id = table_3.table_2_id
                      AND    table_3.id = 5)

Seems like MySQL won't let me have table_1 in the nested SELECT. Suggestions?

I'm not sure why it wouldn't... It would, after all, execute the SELECT before executing the DELETE. What error are you getting? — Dan J
– Dan J, Commented May 17, 2011 at 23:56

Ike Walker · Accepted Answer · 2011-05-18 01:43:27Z

4

You're probably getting this error:

ERROR 1093 (HY000): You can't specify target table 'table_1' for update in FROM clause.

You should be able to do this using MySQL's multiple-table DELETE syntax instead:

DELETE table_1.*
FROM   table_1,
       table_2,
       table_3
WHERE  table_1.id = table_2.table_1_id
AND    table_2.id = table_3.table_2_id
AND    table_3.id = 5

The above query should work, but as a general rule I would recommend using ANSI JOIN syntax instead:

DELETE table_1.*
FROM   table_1
INNER JOIN table_2 on table_2.table_1_id = table_1.id
INNER JOIN table_3 on table_3.table_2_id = table_2.id
WHERE table_3.id = 5

edited May 18, 2011 at 1:43

answered May 18, 2011 at 0:04

Ike Walker

65.8k14 gold badges115 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

StackOverflowNewbie Over a year ago

@Ike - yes, that's the error I'm getting. The problem is that I need to delete multiple records, not just 1 specified by id = . That is why I was trying to use IN and a nested select.

Ike Walker Over a year ago

You can delete multiple rows with the multiple-table delete syntax. Just take your nested SELECT and turn it into a DELETE. I just copied the code from your example. Please update your example if it's not representative of the actual query you are running.

StackOverflowNewbie Over a year ago

Sorry, the nested select would return many table_1.id. That is why I used IN. Does that help?

Ike Walker Over a year ago

Not really :) If you complete the example query in your question I will show you how to solve it with multiple-table delete syntax. Show me the actual SQL you are running (no "....") and I can help you.

StackOverflowNewbie Over a year ago

@Ike - I modified the SQL per your request.

Collectives™ on Stack Overflow

MySQL - How to delete from table when nested select uses that table?

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related