ERROR: You can't specify target table 'wpp' for update in FROM clause-CodePudding

I am trying to UPDATE table value where meta key value _stock_status and id value list of ids, but i got an error (You can't specify target table 'wpp' for update in FROM clause). please advise here is my query

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)
)

thank you

Need to update column value where meta_key = '_stock_status' and id = [1,2,3,4]

CodePudding user response：

I found solution, below query works fine for me.

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 
)

Thanks for your valuable time.

CodePudding user response：

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'