Skip to content

Instantly share code, notes, and snippets.

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

Martydesign commented Feb 16, 2019

Great, this is very helpful. Is there any way to update a price only if the price is lower than 100$? Is it:
UPDATE wp_postmeta SET meta_value = meta_value*0.8 WHERE meta_key = '_sale_price' AND meta_value <= '100'
?

@joeyblack835
Copy link

joeyblack835 commented Feb 20, 2019

Great, this is very helpful. Is there any way to update a price only if the price is lower than 100$? Is it:
UPDATE wp_postmeta SET meta_value = meta_value*0.8 WHERE meta_key = '_sale_price' AND meta_value <= '100'
?

Please be aware of two things.

  1. Use 100 instead of '100'. You should compare numbers not strings.
    Also add a condition to avoid warnings.
    Also you might want to update only lower than 100 not equal.
    UPDATE wp_postmeta SET meta_value = meta_value*0.8 WHERE meta_key = '_sale_price' AND meta_value != '' AND meta_value < 100

  2. Do not forget to update '_price' fields following the logic of woocommerce.

@Martydesign
Copy link

Great, this is very helpful. Is there any way to update a price only if the price is lower than 100$? Is it:
UPDATE wp_postmeta SET meta_value = meta_value*0.8 WHERE meta_key = '_sale_price' AND meta_value <= '100'
?

Please be aware of two things.

  1. Use 100 instead of '100'. You should compare numbers not strings.
    Also add a condition to avoid warnings.
    Also you might want to update only lower than 100 not equal.
    UPDATE wp_postmeta SET meta_value = meta_value*0.8 WHERE meta_key = '_sale_price' AND meta_value != '' AND meta_value < 100
  2. Do not forget to update '_price' fields following the logic of woocommerce.

Thank you Joey!

@curdaneta
Copy link

Hello. Is there any way to update the current _sale_price increasing it's value 35%?

@sczimmerman
Copy link

Hello. Is there any way to update the current _sale_price increasing it's value 35%?

You should be able to do this by changing
SET meta_value = meta_value*0.8
to
SET meta_value = meta_value*1.35

@yanknudtskov
Copy link
Author

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

@Enforcer69
Copy link

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

@yanknudtskov
Copy link
Author

yanknudtskov commented Mar 3, 2020

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

@Enforcer69
Copy link

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.

@yanknudtskov
Copy link
Author

Great :-) Thanks for pitching in

@fubrus
Copy link

fubrus commented Sep 2, 2020

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

@Sergey15159
Copy link

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

@viniciusaac
Copy link

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?

@viniciusaac
Copy link

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.

@leonelcbraz
Copy link

That worked. Thanks!

@moneticc
Copy link

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!

@Jaethon
Copy link

Jaethon commented Jun 10, 2021

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

@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

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