使用嵌套选择查询删除MySQL

I have the following MySQL query:

我有以下MySQL查询：

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

您无法在FROM子句中为更新指定目标表'catalog'

Could someone advise on where I'm going wrong?

有人可以告诉我哪里出错了吗？

2 个解决方案

#1

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

它愚弄了mysql

#2

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.

使用您的查询您无法在FROM子句中为更新指定目标表'catalog'，因为您无法在一个查询中对同一个表进行选择和更新。

#1