mysql 错误：不能在FROM子句中指定要更新的目标表'wpp'

xzv2uavs 于 2022-11-21 发布在 Mysql

关注(0)|答案(2)|浏览(171)

我试图更新表值，其中 metakey value _stock_status和id值的id列表，但我得到了一个错误（你不能指定目标表'wpp'在FROM子句中更新）。请告知这里是我的查询

UPDATE meta_post AS wpp
SET wpp.meta_value = 'instock' 
WHERE wpp.meta_key = '_stock_status'
AND wpp.id IN (
  SELECT DISTINCT id
  FROM meta_post 
  WHERE meta_key = '_stock' 
  AND (meta_value BETWEEN 2 AND 4)
)

谢谢你
需要更新列值，其中 meta_key = '_stock_status'且id = [1，2，3，4]

mysql

来源：https://stackoverflow.com/questions/74472419/error-you-cant-specify-target-table-wpp-for-update-in-from-clause

2条答案

按热度按时间

bn31dyow1#

我找到了解决方案，下面的查询对我来说很好。

UPDATE meta_post AS wpp
SET wpp.meta_value = 'instock' 
WHERE wpp.meta_key = '_stock_status' 
AND wpp.id IN (
 SELECT * FROM (
 SELECT DISTINCT id FROM meta_post 
 WHERE meta_key = '_stock'
 AND (meta_value BETWEEN 2 AND 4)
 ) AS pids 
)

感谢您宝贵的时间。

赞(0）回复(0）举报 2022-11-21

dwbf0jvd2#

UPDATE meta_post AS wpp
JOIN (
  SELECT id
  FROM meta_post 
  WHERE meta_key = '_stock' 
    AND meta_value BETWEEN 2 AND 4
  ) tmp USING (id)
SET wpp.meta_value = 'instock' 
WHERE wpp.meta_key = '_stock_status'

赞(0）回复(0）举报 2022-11-21

我来回答

mysql 错误：不能在FROM子句中指定要更新的目标表'wpp'

2条答案

相关问题

热门标签

最新问答