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

You also have to delete price caching. Otherwise you won't see any changes.

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

Thanks :-)

Hello, how to update prices for products in a certain category only?

You could probably do something like this to get all products in a category,

SELECT ID as post
INNER JOIN wp_term_relationships rs ON rs.object_id = post.ID
WHERE post_type = "post" //post type post for my example
AND post_status = "publish"
AND rs.term_taxonomy_id = 1 //term_taxanomy_id is 1 for my example

You could then use something like this to update it

UPDATE wp_postmeta SET meta_value = meta_value*0.8 WHERE meta_key = '_regular_price' AND meta_value != '' IN (**See Query from Above**)

You could probably do something like this to get all products in a category,

SELECT ID as post
INNER JOIN wp_term_relationships rs ON rs.object_id = post.ID
WHERE post_type = "post" //post type post for my example
AND post_status = "publish"
AND rs.term_taxonomy_id = 1 //term_taxanomy_id is 1 for my example

You could then use something like this to update it

UPDATE wp_postmeta SET meta_value = meta_value*0.8 WHERE meta_key = '_regular_price' AND meta_value != '' IN (**See Query from Above**)

Thank you for anwer.
But is more likely:
SELECT ID as post FROM wp_posts
INNER JOIN wp_term_relationships rs ON rs.object_id = ID
WHERE post_type = "product"
AND post_status = "publish"
AND rs.term_taxonomy_id = 1; // or AND rs.term_taxonomy_id IN (1,2,3...X) - categories ID

To get categories ID (term_taxonomy_id):
SELECT t., tt.
FROM wp_terms AS t
INNER JOIN wp_term_taxonomy AS tt
ON t.term_id = tt.term_id
WHERE tt.taxonomy ='product_cat'
ORDER BY t.name ASC;

To update products prices on selected categories:
UPDATE wp_postmeta SET meta_value = meta_value*0.8 WHERE meta_key = '_regular_price' AND meta_value != '' AND post_id IN(first query);

Hope it helps.

Great :-) Thanks for pitching in

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

Hello, help how to change the price variation for a particular category?

Hello, can anyone help me? When I set a _sale_price and _price on a product, it got on_sale. But when I remove the _sale_price value and uses _price = _regular_price. The product keep showing like "on_sale". Can anyone please help me?

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