Updating product prices cache issue using a SQL query in Woocommerce

Question

I have a SQL script that is supposed to synchronize and adjust regular price comparing the text document uploaded daily on server.

New prices are written to database, and I see them when looking at database. They show in _regular_price field properly. Problem is that frontend shows old prices .. until I manually just re-update each product in backend. Since i have thousands of products it is ineffective and tedious.

What am I missing?

Answer 1

Variable product prices are cached in wp_options table as transient…

So you will need also to delete through SQL using for each variable product ID something like this:

DELETE FROM `wp_options` WHERE `wp_options`.`option_name` LIKE '_transient_timeout_wc_var_prices_1234'
DELETE FROM `wp_options` WHERE `wp_options`.`option_name` LIKE '_transient_wc_var_prices_1234'

Where 1234 (at the end) is the variable product ID.

So programmatically (where $product_id is the dynamic variable product ID):

global $wpdb;

$wpdb->query( "
    DELETE FROM {$wpdb->prefix}options 
    WHERE {$wpdb->prefix}options.option_name LIKE '_transient_timeout_wc_var_prices_$product_id'
" );

$wpdb->query( "
    DELETE FROM {$wpdb->prefix}options 
    WHERE {$wpdb->prefix}options.option_name LIKE '_transient_wc_var_prices_$product_id'
" );

This will remove the targeted variable product cache…

---

Other products (simple for example) not cached… When updating prices there is 2 cases:

1) The product is on sale:

• _price and _sale_price will have the discounted product price.
• _regular_price will have the normal product price (non discounted)

2) The product is NOT on sale:

• _price and _regular_price will have the normal product price.
• _sale_price will be empty

So _price and _regular_price need always to be updated…