Woocommerce set sale price to regular price - 15% discount

Question

I have a lot of products (nearly 30,000) in woocommerce. The regular price has been set properly, now I want to set the sale price to the regular one with some percentage of discount. I tried with some plugin but I had always a PHP memory error due to the numbers of products. So I'm trying to solve the problem directly in MYSQL.

Here is my SQL code:

UPDATE s SET s.meta_value = r.meta_value * 0.85, FROM wp_postmeta s INNER JOIN wp_postmeta r ON s.post_id = r.post_id WHERE s.meta_key = '_sale_price' AND r.meta_key = '_regular_price'

But there is an error in SQL syntax. Please suggest to me how to correct the code.

Answer 1

I just found the answer - after a lot of digging:

UPDATE wp_postmeta AS s
LEFT JOIN wp_postmeta AS r
ON s.post_id = r.post_id
SET s.meta_value = r.meta_value * 85 / 100
WHERE s.meta_key = '_sale_price'
AND r.meta_key = '_regular_price'

Everything is ok in DB but now the problem is I can't see the modification on product pages. Only if I enter the product in the admin and then save from there the sale price is refreshed in the product page. Anyone know why?

Answer 2

Alessandro, it's actually working. But you're at a half way to success:

UPDATE wp_postmeta AS s
LEFT JOIN wp_postmeta AS r
ON s.post_id = r.post_id
SET s.meta_value = r.meta_value
WHERE s.meta_key = '_sale_price'
AND r.meta_key = '_regular_price'

UPDATE wp_postmeta AS s
LEFT JOIN wp_postmeta AS r
ON s.post_id = r.post_id
SET s.meta_value = r.meta_value
WHERE s.meta_key = '_price'
AND r.meta_key = '_sale_price'

That way you have sale price and the "current" price updated. You'll have to run both these scripts separately when adding any new product. AND don't forget to clear the cache with any caching plugin such as W3 Total Cache or similar.

p.s.: Good if future Woocommerce versions will include the feature to switch "current" price to _sale or _regular.

Answer 3

You could just permanently filter the sale price via the woocommerce_get_sale_price filter.

function so_31493236_sale_price( $sale_price, $product ) {
    $sale_price = $product->get_regular_price() * 0.15;
    return $sale_price;
}
add_filter( 'woocommerce_get_sale_price', 'so_31493236_sale_price', 10, 2 );