MySQL delete with nested select query

Question

I have the following MySQL query:

DELETE FROM catalogue 
WHERE catalogue_id IN (
  SELECT catalogue_id 
  FROM catalogue 
  WHERE (
    product_id = (SELECT product_id FROM catalogue WHERE catalogue_id = '2290') 
    AND length_id = (SELECT length_id FROM catalogue WHERE catalogue_id = '2290') 
    AND gauge_id = (SELECT gauge_id FROM catalogue WHERE catalogue_id = '2290')
  )
)

But when I attempt to execute I get the following error message:

You can't specify target table 'catalogue' for update in FROM clause

Could someone advise on where I'm going wrong?

score 19 · Accepted Answer · answered Sep 04 '11 at 10:15

19

Perform double nesting

DELETE FROM catalogue 
WHERE catalogue_id IN (SELECT catalogue_id FROM (
  SELECT catalogue_id 
  FROM catalogue 
  WHERE (
    product_id = (SELECT product_id FROM catalogue WHERE catalogue_id = '2290') 
    AND length_id = (SELECT length_id FROM catalogue WHERE catalogue_id = '2290') 
    AND gauge_id = (SELECT gauge_id FROM catalogue WHERE catalogue_id = '2290')
  )) x
)

It fools mysql

answered Sep 04 '11 at 10:15

zerkms

249,484
69
436
539

1

Works perfectly zerkms, but what's the reason for having to 'fool' MySQL? – bland_dan Sep 04 '11 at 10:29
1

What about if you have more than one `WHERE` column? i.e. `DELETE FROM catalogue WHERE catalogue_id AND catalogue_name IN...` – Sep 11 '14 at 13:35
@ThreaT: I'm not sure what you're asking about. Multiple predicates may be added with `ADD`, yes. – zerkms Sep 11 '14 at 20:05
How would you change your answer to use `ADD`? – Sep 12 '14 at 08:24
@ThreaT: ask a separate question then. PS: it's `AND` not `ADD` sorry :-) – zerkms Sep 12 '14 at 09:12
1

It's been 9 years and still need this hackery. – Krum Jan 13 '20 at 20:08

score 5 · Answer 2 · answered Sep 04 '11 at 10:26

Or you can use temporary table:

  CREATE TEMPORARY TABLE t AS
  SELECT catalogue_id 
  FROM catalogue 
  WHERE (
    product_id = (SELECT product_id FROM catalogue WHERE catalogue_id = '2290') 
    AND length_id = (SELECT length_id FROM catalogue WHERE catalogue_id = '2290') 
    AND gauge_id = (SELECT gauge_id FROM catalogue WHERE catalogue_id = '2290')
  );

  DELETE FROM catalogue WHERE catalogue_id IN (SELECT catalogue_id FROM t);

With your query you got You can't specify target table 'catalogue' for update in FROM clause because you can't make select and update on the same table in one query.

Thanks Kamil for the explanation. – bland_dan Sep 04 '11 at 10:31 — bland_dan, Sep 04 '11 at 10:31

MySQL delete with nested select query

2 Answers2

Linked