Queries for updating all prices including variations in WooCommerceIn this instance all prices are subtracted 20% (0.8)#woocommerce #mysql

woocommerce-update-prices.sql

UPDATE wp_postmeta SET meta_value = meta_value*0.8 WHERE meta_key = '_regular_price' AND meta_value != ''
UPDATE wp_postmeta SET meta_value = meta_value*0.8 WHERE meta_key = '_sale_price' AND meta_value != ''
UPDATE wp_postmeta SET meta_value = meta_value*0.8 WHERE meta_key = '_price' AND meta_value != ''
UPDATE wp_postmeta SET meta_value = meta_value*0.8 WHERE meta_key = '_regular_price_tmp' AND meta_value != ''
UPDATE wp_postmeta SET meta_value = meta_value*0.8 WHERE meta_key = '_sale_price_tmp' AND meta_value != ''
UPDATE wp_postmeta SET meta_value = meta_value*0.8 WHERE meta_key = '_price_tmp' AND meta_value != ''
UPDATE wp_postmeta SET meta_value = meta_value*0.8 WHERE meta_key = '_min_variation_price' AND meta_value != ''
UPDATE wp_postmeta SET meta_value = meta_value*0.8 WHERE meta_key = '_max_variation_price' AND meta_value != ''
UPDATE wp_postmeta SET meta_value = meta_value*0.8 WHERE meta_key = '_min_variation_regular_price' AND meta_value != ''
UPDATE wp_postmeta SET meta_value = meta_value*0.8 WHERE meta_key = '_max_variation_regular_price' AND meta_value != ''
UPDATE wp_postmeta SET meta_value = meta_value*0.8 WHERE meta_key = '_min_variation_sale_price' AND meta_value != ''
UPDATE wp_postmeta SET meta_value = meta_value*0.8 WHERE meta_key = '_max_variation_sale_price' AND meta_value != ''
DELETE FROM wp_options WHERE (option_name LIKE '_transient_wc_var_prices_%' OR option_name LIKE '_transient_timeout_wc_var_prices_%')

Hello. I have more then 3000 products. Is there any way to set only _sale_price with sql query?

To set _sale_price you need to set _price as well.

That worked. Thanks!

Hello. I have more than 3000 products. Is there any way to set only _sale_price with sql query?

Yeah, that would be handy also for my 700 products. Maybe someone can help - is there any way how we could set

1. _sale_price for example for 50% for all variations?
   and in the same time also:
2. _sale_price_dates_to date to some future timestamp?

Thanks!

Hi Yan and others. This thread is the closest I've come to seeing what I'm looking for. I wonder if this might make sense to one of you.

The products we have (8000) do not have a price set, but have Gravity Forms to calculate the price on the product page — which is very slow as you can imagine.

The price is roughly 2x(height+width). If I could run a query that would do this directly in the database, that would be amazing.

pa_artwidth and pa_artheight are the attributes.

I've run some basic woocommerce queries in phpmyadmin, but nothing that gets complicated enough for what I need.

If this rings a bell with anyone or if anyone could point me somewhere that could help, I'd appreciate it. Thank you!

-George

Just to add to this thread. In most instances, added or deducting a percentage will result in lots of decimalisation of the value. To round it to two decimal places, the query should be updated to include ROUND.

UPDATE wp_postmeta SET meta_value = ROUND(meta_value*0.8, 2) WHERE meta_key = '_regular_price' AND meta_value != ''

Hi @yanknudtskov
I tried on your command on an installation without the plugin WPML it's working like a charm but on with WPML I have this error:

UPDATE wp_postmeta SET meta_value = meta_value*1.10 WHERE meta_key = '_max_variation_regular_price' AND meta_value != '';
MySQL said: Documentation

#1292 - Truncated incorrect DOUBLE value: 94.9979.99

I tried to increase all price by 10 %

UPDATE wp_postmeta SET meta_value = meta_value*1.10 WHERE meta_key = '_regular_price' AND meta_value != '';
UPDATE wp_postmeta SET meta_value = meta_value*1.10 WHERE meta_key = '_sale_price' AND meta_value != '';
UPDATE wp_postmeta SET meta_value = meta_value*1.10 WHERE meta_key = '_price' AND meta_value != '';
UPDATE wp_postmeta SET meta_value = meta_value*1.10 WHERE meta_key = '_regular_price_tmp' AND meta_value != '';
UPDATE wp_postmeta SET meta_value = meta_value*1.10 WHERE meta_key = '_sale_price_tmp' AND meta_value != '';
UPDATE wp_postmeta SET meta_value = meta_value*1.10 WHERE meta_key = '_price_tmp' AND meta_value != '';
UPDATE wp_postmeta SET meta_value = meta_value*1.10 WHERE meta_key = '_min_variation_price' AND meta_value != '';
UPDATE wp_postmeta SET meta_value = meta_value*1.10 WHERE meta_key = '_max_variation_price' AND meta_value != '';
UPDATE wp_postmeta SET meta_value = meta_value*1.10 WHERE meta_key = '_min_variation_regular_price' AND meta_value != '';
UPDATE wp_postmeta SET meta_value = meta_value*1.10 WHERE meta_key = '_max_variation_regular_price' AND meta_value != '';
UPDATE wp_postmeta SET meta_value = meta_value*1.10 WHERE meta_key = '_min_variation_sale_price' AND meta_value != '';
UPDATE wp_postmeta SET meta_value = meta_value*1.10 WHERE meta_key = '_max_variation_sale_price' AND meta_value != '';
DELETE FROM wp_options WHERE (option_name LIKE '_transient_wc_var_prices_%' OR option_name LIKE '_transient_timeout_wc_var_prices_%');

i would like to know if you have any hint for me :)

@marceloeatworld I think moded-mike's comment above yours regarding rounding is relevant to your use case. If you haven't already, try changing meta_value*1.10 to ROUND(meta_value*1.10, 2).

Hi @doubleedesign Thanks for your reply :) i have the same error but with this command it's working, but the result wasn't good.
UPDATE wp_postmeta SET meta_value = 'meta_value*1.10' WHERE meta_key = '_max_variation_regular_price' AND meta_value != '';