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
Copy link

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
Copy link

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

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
Copy link

curdaneta commented Jan 16, 2020

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

@sczimmerman
Copy link

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
Copy link
Author

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
Copy link

Enforcer69 commented Mar 3, 2020

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

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
Copy link
Author

yanknudtskov commented Mar 4, 2020

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

Sergey15159 commented Oct 24, 2020

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

@viniciusaac
Copy link

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
Copy link

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
Copy link

leonelcbraz commented Dec 30, 2020

That worked. Thanks!

@monetic
Copy link

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

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