MySQL删除与嵌套选择查询 [英] MySQL delete with nested select query

问题描述

我有以下MySQL查询:

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:

您不能在FROM子句中指定目标表目录"进行更新

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

有人可以告诉我我要去哪里哪里吗?

Could someone advise on where I'm going wrong?

推荐答案

执行双重嵌套

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
)

它愚弄了mysql

这篇关于MySQL删除与嵌套选择查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！