MySQL-1093 - You can't specify target table 'product' for update in FROM clause

最新推荐文章于 2024-08-21 08:47:50 发布

小六儿爱吃鱼

最新推荐文章于 2024-08-21 08:47:50 发布

阅读量1.2k

点赞数 1

分类专栏： mysql</div>

本文链接：https://blog.csdn.net/qq_27465795/article/details/79024450

版权

mysql</div> 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

原来的SQL语句

UPDATE product p
SET p.price_strategy = 'FREE'
WHERE 
 p.id IN (
  SELECT
   p1.id
  FROM
   product p1
  LEFT JOIN price_info price ON p1.id = price.product_id
  WHERE
   p1.tag = 1
  AND price.price_strategy = 'FREE'
 );

执行时，报[Err] 1093 - You can't specify target table 'product' for update in FROM clause

原因：“In general, you cannot modify a table and select from the same table in a subquery.”

如下，加一层嵌套即可

UPDATE product p
SET p.price_strategy = 'FREE'
WHERE
 p.id IN (
  SELECT
   temp.id
  FROM
   (
    SELECT
     p1.id
    FROM
     product p1
    LEFT JOIN price_info price ON p1.id = price.product_id
    WHERE
     p1.tag = 1
    AND price.price_strategy = 'FREE'
   ) temp
 );