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_%')

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 != '';