Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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_%')
@doubleedesign

This comment has been minimized.

Copy link

@doubleedesign doubleedesign commented Feb 19, 2018

Thanks for sharing, you just saved me quite a bit of time which in turn has saved my client hoooourrrrrsss of manual updating! :)

@1manfactory

This comment has been minimized.

Copy link

@1manfactory 1manfactory commented May 17, 2018

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

@Martydesign

This comment has been minimized.

Copy link

@Martydesign 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

This comment has been minimized.

Copy link

@joeyblack835 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

This comment has been minimized.

Copy link

@Martydesign Martydesign commented Mar 8, 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.

Thank you Joey!

@curdaneta

This comment has been minimized.

Copy link

@curdaneta curdaneta commented Jan 16, 2020

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

@sczimmerman

This comment has been minimized.

Copy link

@sczimmerman sczimmerman commented Feb 13, 2020

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

This comment has been minimized.

Copy link
Owner Author

@yanknudtskov yanknudtskov commented Feb 13, 2020

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

This comment has been minimized.

Copy link

@Enforcer69 Enforcer69 commented Mar 3, 2020

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

@yanknudtskov

This comment has been minimized.

Copy link
Owner Author

@yanknudtskov 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

This comment has been minimized.

Copy link

@Enforcer69 Enforcer69 commented Mar 4, 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**)

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

This comment has been minimized.

Copy link
Owner Author

@yanknudtskov yanknudtskov commented Mar 4, 2020

Great :-) Thanks for pitching in

@fubrus

This comment has been minimized.

Copy link

@fubrus 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

This comment has been minimized.

Copy link

@Sergey15159 Sergey15159 commented Oct 24, 2020

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

@viniciusaac

This comment has been minimized.

Copy link

@viniciusaac viniciusaac commented Nov 4, 2020

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

This comment has been minimized.

Copy link

@viniciusaac viniciusaac commented Nov 4, 2020

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

This comment has been minimized.

Copy link

@leonelcbraz leonelcbraz commented Dec 30, 2020

That worked. Thanks!

@monetic

This comment has been minimized.

Copy link

@monetic monetic commented Mar 10, 2021

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

This comment has been minimized.

Copy link

@Jaethon 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

This comment has been minimized.

Copy link

@moded-mike moded-mike commented Jun 21, 2021

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

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