Skip to content

Instantly share code, notes, and snippets.

@yanknudtskov

yanknudtskov/woocommerce-update-prices.sql

Last active August 1, 2023 23:45
Show Gist options
  • Star 24 You must be signed in to star a gist
  • Fork 13 You must be signed in to fork a gist
  • Save yanknudtskov/9e725d5d3018bfc1bfe62db59ee80186 to your computer and use it in GitHub Desktop.
Save yanknudtskov/9e725d5d3018bfc1bfe62db59ee80186 to your computer and use it in GitHub Desktop.
Download ZIP
Queries for updating all prices including variations in WooCommerceIn this instance all prices are subtracted 20% (0.8)#woocommerce #mysql
Raw
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_%')
@moded-mike
Copy link

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

@marceloeatworld
Copy link

marceloeatworld commented Dec 12, 2022 •
edited

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 :)

@doubleedesign
Copy link

@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).

@marceloeatworld
Copy link

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment