Question 22230 in Mysql Question posted in
The official MySQL documentation can be found here.

ERROR: You can't specify target table 'wpp' for update in FROM clause – Mysql

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]

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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.


  2. 0 
    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'
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search