CODEWITHSUNDEEP
sqlwordpresswoocommerceproduct-variations
GazzaC
GazzaC

Reputation: 13

Increase all product prices by a percentage including variations in Woocommerce

With woocommerce I would like to update all my prices by a percentage. A lot of my products are variable (with multiple variations) so I need every price on the site to increase, not just simple products.

I have tried a number of sql queries but they only set the price to 0 and dont affect the variation prices.

Im using the latest versions of Wordpress and Woocommerce.

Any help would be great.

Upvotes: 0

Views: 3601

Answers (1)

LoicTheAztec
LoicTheAztec

Reputation: 253784

Update (updated the sale product query)

You should need 4 SQL queries:

  • To update the active price
  • To update the regular price
  • To update the sale price (updated)
  • To delete the cached variation prices

The updated prices are rounded with 2 decimal digits precision.

You can run that four at the same time, but make a database backup before. Also check that the suffix of your database is wp_ and if not replace by your tables suffix.

The 4 SQL queries (replace 1.25 by your corresponding rate percentage):

UPDATE wp_postmeta
SET meta_value = ROUND(meta_value * 1.25, 2)
WHERE meta_key = '_price';

UPDATE wp_postmeta
SET meta_value = ROUND(meta_value * 1.25, 2)
WHERE meta_key = '_regular_price';

UPDATE wp_postmeta
SET meta_value = ROUND(meta_value * 1.25, 2)
WHERE meta_key = '_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_%');

Tested and works for all product types include product variation post type.

Upvotes: 5

Related Questions