`

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

阅读更多

原来的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
 );

 

分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics