Skip to main content

Remove all WooCommerce Sale Prices at once

admin Resources, Snippets

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

 

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

  2. You my friend are a genius. It´s a simple solution, yet it makes us a big big favor. I wish you all the best for your decision to share this with us.

    Reply

  3. 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 🙂

    Reply

    1. Hi Peter – this is great! Question: This process doesn’t remove the ‘Sale’ icon that is auto added in Woocommerce. Any ideas on this so I don’t have to spend the time and my clients money going through 187 items? 🙂 Thanks so much!

      Reply

    2. Yes just do as peter says. There is no point of going in databse and change stuff when this options is available in WOO.

      Reply

    3. it’s not working for me!

      Regular price is ok sale price is blank but in home page it gets with sale price and when i add new products i also have 20% decrease for all new products.

      Reply

  5. Kind of a SILLY solution really. Why not just use the YITH Bulk Product Editing plugin to bulk remove the sale price….DONE

    Reply

  8. 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’;

    Reply

    1. I get this error:
      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’
      MySQL said: Documentation

      #1054 – Unknown column ‘‘_price’’ in ‘where clause’

      Reply

Leave a Reply