Search code examples
sqlmysqlwordpresswoocommerce

Update all product variation sale prices of a Woocommerce variable product

I have about 10 products with close to 200 variations each. I need to update the sale prices for a specific product, including all its variations.

The problem is:

All variations have different prices. No sale prices have been entered - all sale price input boxes are empty.

I need either a SQL solution to run and update all sale prices for the specific product by taking the regular price and deducting 350 from it, OR

Any other way that i am not aware of at this stage as I have tried many solutions including Woocommerce built in solution "set sale price", again which does not work as the prices are not the same.

SELECT * FROM `wp_postmeta` WHERE post_id = "1097"
UPDATE `meta_id` = (SELECT * FROM `wp_postmeta` WHERE `meta_value` = _regular_price) - 350 WHERE `meta_key` = _sale_price

Edited query as research on Stackoverflow suggests that wp_postmeta table holds values for products and wp_posts table holds product id's.

Here is a screen shot of the problem I face:

)

You will see the meta_id is different and the post_id is the same, this is because in WordPress the post_id is the product, in this case on with many variations of itself, and meta_id is the _sale_price part I need to update FOR EACH VARIATION

I hope this makes sense!

Solution

  • Try this (where line AND p.ID = 19 ==> 19 is your variable product ID):

    UPDATE wp_postmeta as pm
JOIN  wp_postmeta as pm2 ON pm.post_id = pm2.post_id
SET pm.meta_value = ( pm2.meta_value - 350 )
WHERE pm.meta_key LIKE '_sale_price'
  AND pm2.meta_key LIKE '_regular_price'
  AND pm.post_id IN
    ( SELECT p2.ID
     FROM wp_posts AS p
     JOIN wp_posts AS p2 ON p2.post_parent = p.ID
     WHERE p.post_type = 'product'
       AND p.post_status = 'publish'
       AND p.ID = 19
       AND p2.post_type = 'product_variation'
       AND p2.post_status = 'publish' );

    Tested and works (always make a backup before)

    So in this SQL query for a defined variable product ID, It will replace all product variations sale prices by the regular price minus 350 …

    enter image description here

    As you can see it works… no errors