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.
Log into phpMyAdmin and click on “SQL” – we will enter some SQL code into here.
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.
Before the SQL query
After the SQL query
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
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.
Increase all the selected products price by 1. This will automatically update all the prices of the selected products with an added $1.
After that, do exactly the same thing, but decrease the price by $1, bringing them back to the same regular price.
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!
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!