Importing price changes from CSV or changing in DB sometimes shows the wrong price on your WooCommerce site Print

  • 0

Problem Description

  • You're importing prices for products in WooCommerce in bulk, probably from CSV or XML file
  • You're specifying sale prices via the `_sale_price` meta/field
  • When viewing the product the correct price is not shown
  • A sale either just started or just finished and you're trying to adjust the prices to accommodate for that change

Problem Resolution

In a WooCommerce database, each product has three price fields:
- _price
- _regular_price
- _sale_price

The first one is the value that is shown as the actual price on the product page. When you edit the properties for and save a product using the WordPress admin WooComm determines if the sale is currently on (based on the schedule) and then saves not just the _sale_price as you've set it but also updates the _price if the sale is determined to be currently on. Similarly when the sale ends, WooCommerce will automatically update the _price field accordingly.

However when you update _regular_price and _sale_price directly in the DB, the _price Field is never changed since direct DB updates do not run any of WooCommerce's automations - WooCommerce doesn't and can't know to do that.

When you're doing manual database updates, you must follow these rules for database updates:

  1. If the sale is to begin immediately after you make your price changes (or has already begun), then you must update _price as well as _sale_price at the same time.
  2. If the sale has just ended and WooCommerce didn't automatically change the prices back to regular price (ex: if you didn't specify a sale end date), you must update both _price and _regular_price (although _regular_price may already be correct).
  3. If the sale is to start in the future and you're setting the start/end dates, you can update just _sale_price (and of course the start/end dates) as WooCommerce's cron job will update _price on the day the products are to go on sale

Was this answer helpful?

← Back