MySQL query to update field in a table based on value of other field in same table

I’ve been trying to figure out the correct query for the following issue and I cannot seem to find the solution myself.

I’m running a WooCommerce store which stores a bunch of values as ‘meta_key’ in a MySQL table with the value stored in the ‘meta_value’ column.

What I want to do is update the ‘meta_value’ of ‘meta_key’ ‘pisol_exact_lot_arrival_date’ to a specific date based on an SKU search on the ‘meta_value’ ‘_sku’ column.

I wrote the below query but I’m getting an error when I execute this: “#1093 – You can’t specify target table ‘wp_postmeta’ for update in FROM clause”.

UPDATE `wp_postmeta`
SET `meta_value` = '31/03/2022'
WHERE `post_id` =
    (SELECT post_id
     FROM wp_postmeta
     WHERE meta_value = 'MM083ZM/A')
  AND `meta_key` = 'pisol_exact_lot_arrival_date'

This is what my table looks like.

I hope someone can help me. Thank you in advance!

Answer

You can make a temporary table so that mysql doesn’t give that erorr any more.

UPDATE `wp_postmeta`
SET `meta_value` = '31/03/2022'
WHERE `post_id` =
    (SELECT post_id
     FROM (SELECT * FROM wp_postmeta) wpp
     WHERE meta_value = 'MM083ZM/A')
  AND `meta_key` = 'pisol_exact_lot_arrival_date'