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

Question

I don't know how many products are like this, but I know there are many.

in the db;

The _regular_price value of the post(wc product) is higher than the _price value. I don't know how or when this happened, but the front end is displaying _price - the lower of the two. ( this is bad, we are losing money )

I am trying to run a query that will find all of these post_ids so that I can update them. I would like to update _price with the value of _regular_price

at first I tried this;

SELECT post_id 
FROM wp_postmeta 
WHERE meta_key='_regular_price' > meta_key='_price' 

this returns a very long list of post_id many duplicates, and not always a product.

I then tried to adapt a different answer from stackexchange I came up with this

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'

however I am not exactly understanding inner join and this query does not return the desired result either.

I believe i need to join wp_posts & wp_postmeta here becuase post_type='product' would be pulled from wp_posts & meta_key='_regular_price' > meta_key='_price' would be pulled from wp_postmeta

could it be that meta_key='_regular_price' is not returning an integer?

my next thought was to compare these values with a function, and then update if needed ie;

//functions.php
add_action( 'after_theme_setup', 'benz_update_wc_meta' );
function benz_update_wc_meta() {
  global $woocommerce, $post;
  $reg_price = get_post_meta( get_the_ID(), '_regular_price', true );
  $basic_price = get_post_meta( get_the_ID(), '_price', true );
  if ( $reg_price > $basic_price ) {
    update_post_meta( get_the_ID(), '_price', $reg_price );
  }
}

or something like that -- but no luck there either.

Let me know if you have any advice. thanks.

I have also tried echoing out

$reg_price = get_post_meta( get_the_ID(), '_regular_price', true );

in the theme template woocommerce files -- and this displays the correct price, but when you add product to cart, the wrong price is still used.

Answer 1

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.