CODEWITHSUNDEEP
sqlmysqlwordpresswoocommerce
Andre van Rensburg
Andre van Rensburg

Reputation: 161

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!

Upvotes: 2

Views: 3150

Answers (2)

fearis
fearis

Reputation: 499

Based on @LoicTheAztec I've extended it to limit variation by meta_value:

## Get all Variation ID for parent 3859 which have meta_value like %bound-soft%
SET @ids = (SELECT GROUP_CONCAT(p2.ID) 
     FROM wp_posts AS p
     JOIN wp_posts AS p2 ON p2.post_parent = p.ID
     INNER JOIN wp_postmeta pm2 ON pm2.post_id = p2.ID
     WHERE p.post_type = 'product'
       AND p.post_status = 'publish'
       AND p.ID = 3859
       AND p2.post_type = 'product_variation'
       AND p2.post_status = 'publish' 
AND pm2.meta_value LIKE '%bound-soft%');

## Set price to 10 based on variation ids
UPDATE wp_postmeta as pm
SET pm.meta_value = '10'
WHERE  FIND_IN_SET(pm.post_id, @ids)
AND pm.meta_key = '_price';

## Price can be '_price' or '_regular_price' or '_sale_price'

## See results of @ids
SELECT @ids

Upvotes: 0

LoicTheAztec
LoicTheAztec

Reputation: 253784

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

Upvotes: 2

Related Questions