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?

zerkms · Accepted Answer · 2011-09-04 10:15:41Z

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 4, 2011 at 10:15

zerkms

256k73 gold badges447 silver badges551 bronze badges

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

6 Comments

bland_dan Over a year ago

Works perfectly zerkms, but what's the reason for having to 'fool' MySQL?

user1191027 Over a year ago

What about if you have more than one WHERE column? i.e. DELETE FROM catalogue WHERE catalogue_id AND catalogue_name IN...

zerkms Over a year ago

@ThreaT: I'm not sure what you're asking about. Multiple predicates may be added with ADD, yes.

user1191027 Over a year ago

How would you change your answer to use ADD?

Krum Over a year ago

It's been 9 years and still need this hackery.

|

Kamil Dziedzic · Accepted Answer · 2011-09-04 10:26:16Z

5

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.

answered Sep 4, 2011 at 10:26

Kamil Dziedzic

5,0813 gold badges33 silver badges49 bronze badges

1 Comment

bland_dan Over a year ago

Thanks Kamil for the explanation.

Collectives™ on Stack Overflow

MySQL delete with nested select query

2 Answers 2

6 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related