Skip to main content

Remove all WooCommerce Sale Prices at once

One of my clients runs a WooCommerce website on WordPress and has 450 products on it, he went in and added a sale price for every single product, so basically every product now is at sale price, not regular price. He now wants to remove all of the sale prices completely and return the website to how it was before.

The problem with removing sale prices is there’s really no fast way of completely removing them, especially if you’ve set up a schedule, you need to go into every product individually and remove them so that it returns to the normal prices. I figured out a way to do it fast, using phpMyAdmin and a free plugin.

1. phpMyAdmin

Log into phpMyAdmin and click on “SQL” – we will enter some SQL code into here.

sql-1

2. Add SQL Codes

DELETE FROM wp_postmeta WHERE meta_key =  '_sale_price';
DELETE FROM wp_postmeta WHERE meta_key =  '_sale_price_dates_to';
DELETE FROM wp_postmeta WHERE meta_key =  '_sale_price_dates_from';

Just to be sure, please back up your database or if you use cPanel, do a complete website backup.

sql-2

3. Results

Before the SQL query

product-1

After the SQL query

product-2

4. The Problem

As you can see, the SQL query has done what it was supposed to do, it deleted all the post meta telling WooCommerce the sale date from and sale date to, however, if you update the product, it will also show the price still as the sale price, but it will no longer show as discounted, it will show as the regular price. This is something to with WooCommerce’s configuration. You’ll notice that if you do a search for the price in mySQL, it actually returns no results – so I’m not completely sure where it is getting the sale price from.

5. The Solution

We need to manually go into each product and then click on publish, which will automatically refresh the product and it will start showing the correct updated regular price. I don’t know about you, but with 450 product, I have no time for that!

The fast way to fix this

Download a plugin called YITH Bulk Product Editing

yith1

 

Install the plugin and go into it, located under “YITH” on your admin sidebar.

You want to bulk edit all the products at once so you can configure the plugin to show all the products at once.

prod1

Increase all the selected products price by 1. This will automatically update all the prices of the selected products with an added $1.

edit-2

After that, do exactly the same thing, but decrease the price by $1, bringing them back to the same regular price.

edit-1

6. The Result

The database has returned to it’s normal prices, sale prices are deleted and no longer in the database. All prices are now reflecting the regular price, no sale prices in sight!

complete-product

It was a bit a of a tricky one, but I hope this workaround helps you! Make sure you back up your site before you do anything like this as if you don’t have a backup – you’re stuffed and then you’ll manually have to edit the products!

Cheers

 

6 thoughts to “Remove all WooCommerce Sale Prices at once”

  1. An easier option is via bulk edit from the products screen.

    It’s not intuitive, but if you set “Sale” to “Change To:” and leave the price blank, it clears the sale price.

    I thought I’d try it, just in case, rather than messing with a SQL query. And it worked 🙂

  2. Here’s a query that should reset the sale price to normal price for all products across the site:
    === BACKUP DATABASE BEFORE EXECUTE THE QUERY ===

    UPDATE `wp_postmeta` AS t1
    INNER JOIN `wp_postmeta` AS t2 ON
    t2.post_id = t1.post_id AND t2.meta_key = ‘_regular_price’ SET t1.meta_value = t2.meta_value
    WHERE t1.meta_key = ‘_price’;
    DELETE FROM wp_postmeta WHERE meta_key = ‘_sale_price’;
    DELETE FROM wp_postmeta WHERE meta_key = ‘_sale_price_dates_to’;
    DELETE FROM wp_postmeta WHERE meta_key = ‘_sale_price_dates_from’;

Leave a Reply