Need a SQL query to update meta_key='_price' with value in meta_key='_regular_price'

The reason your query doesn’t return anything fruitful is because you’re not comparing the meta_value of both of the meta_keys, you’re comparing non-numeric outcomes of meta_key = ‘x’ and meta_key = ‘y’.

SELECT post_id, 
       post_title
FROM wp_posts 
   INNER JOIN wp_postmeta 
       ON post_id = post_id
WHERE post_type="product"
   AND meta_key='_regular_price' > meta_key='_price'

What you want to be doing is comparing the meta_value for these two keys.

SELECT
  price.post_id,
  r_price,
  p_price
FROM
  (
    SELECT
      post_id,
      CAST(meta_value as SIGNED INTEGER) r_price
    FROM
      wp_postmeta
    WHERE
      meta_key = '_regular_price'
  ) regular_price,
  (
    SELECT
      post_id,
      CAST(meta_value as SIGNED INTEGER) p_price
    FROM
      wp_postmeta
    WHERE
      meta_key = '_price'
  ) price
WHERE
  price.post_id = regular_price.post_id
  and r_price > p_price

This will now give you the IDs you’re looking for. You’re best then looping through the result set and updating the post meta.

Related Posts: