MySQL query to change variable product price in WooCommerce

Question

I want to change the prices for variable products in woocommerce. I tried with below MySQL query to do it,

UPDATE wp_postmeta 
SET meta_value = price_value 
WHERE post_id = variation_id AND meta_key = '_sale_price';

but its updating only in backend(DB and admin area) not in front-end. Actually in front-end its not showing sale price also. As I'm not good at SQL, is there any code snippet or WooCommerce hook which allows me to change the price for a variable product?

Thanks.

score 11 · Accepted Answer · edited Feb 15 '20 at 15:36

11

If you want to change the Product Price which is on Sale you have to update both _sale_price and _price with same value.

//for Regular Price
UPDATE wp_postmeta 
SET meta_value = diff_price_value 
WHERE post_id = variation_id AND meta_key = '_regular_price';

//for Price
UPDATE wp_postmeta 
SET meta_value = price_value 
WHERE post_id = variation_id AND meta_key = '_price';

//for Sale Price
UPDATE wp_postmeta 
SET meta_value = price_value 
WHERE post_id = variation_id AND meta_key = '_sale_price';

ADDED If you want to merge all the above query into a single query you can use this one:

UPDATE wp_postmeta
SET meta_value = CASE
                   WHEN meta_key = "_regular_price" THEN diff_price_value
                   WHEN meta_key = "_price" THEN price_value
                   WHEN meta_key = "_sale_price" THEN price_value
                   ELSE meta_key
               END
WHERE post_id = variation_id
  AND meta_key IN ("_regular_price", "_price", "_sale_price");

Also you have to delete WooCommerce product price caching which is stored in wp_options table under _transient_timeout_wc_var_prices_{{post_id}} and _transient_wc_var_prices_{{post_id}} in option_name

DELETE
FROM `wp_options`
WHERE (`option_name` LIKE '_transient_wc_var_prices_%'
    OR `option_name` LIKE '_transient_timeout_wc_var_prices_%')

All the above query is tested and worked for me.

Before running this query do take a database backup

Reference: Copy WooCommerce products Sale prices to regular prices and reset Sale prices

Hope this helps!

edited Feb 15 '20 at 15:36

Farrukh zaman Khan

13
3

answered Jan 09 '17 at 13:13

Raunak Gupta

10,412
3
58
97

thank you. Is there any way they can be combined to a single query,? – karbyl_subbu Jan 09 '17 at 14:20
@karbyl_subbu: I have updated my answer, please give it a try. – Raunak Gupta Jan 09 '17 at 14:34
@RaunakGupta , I need your help [here](http://stackoverflow.com/questions/43805478/how-to-update-sale-price-woocommerce3-0-variable-product) – Prafulla Kumar Sahu May 05 '17 at 13:03
1

For some reason, updating the table just doesn't affect the products' prices. And I've changed all of them (price, regular and sale). Is there some kind of cache or data saved in arrays somewhere? – rantebi Oct 04 '17 at 09:52
@rantebi: Yes you also need to delete all the cache price that is stored in `wp_options` table. Take a look at [this answer](https://stackoverflow.com/a/45879385/5019802), I'll update this answer as well with in few min. – Raunak Gupta Oct 04 '17 at 09:56
1

Thank you! I found a solution, perhaps it's the same as the link you suggested. First I had to make sure I updated all these meta_keys: "_price","_regular_price", "_sale_price", "_min_variation_price", "_max_variation_price", "_min_variation_regular_price", "_max_variation_regular_price", "_min_variation_sale_price", "_max_variation_sale_price" Second, I edited the weight field in all of the products I have (From 0 to 0 with bulk edit) and that probably caused the caches to flush. – rantebi Oct 05 '17 at 19:33

MySQL query to change variable product price in WooCommerce

1 Answers1

Linked