Mass quantity update using wpdb

Hi @guev4ra

When you update the _stock directly in the database, WooCommerce doesn’t know that a change has been made and therefore doesn’t update the related data. This is why you’re experiencing issues with some functionalities.

In addition to _stock, you’ll also need to update _stock_status in the postmeta table, and stock_quantity and stock_status in the wc_product_meta_lookup table.

WooCommerce doesn’t have a built-in function to handle this because direct database updates are not recommended. As you’ve noticed, it can cause issues with other parts of the system.

Instead, it’s recommended to use WooCommerce’s CRUD functions. I understand your concern about performance, but these functions ensure that all related data is updated and all necessary actions are triggered.

Further, WooCommerce itself does not handle caching, this is typically managed by your hosting provider or a plugin. If you have a caching plugin installed, you should clear it after making these updates. Additionally, you can head over to WooCommerce → Status → Tools, and do the following:

• WooCommerce transients – Clear
• Expired transients – Clear
• Clear template cache – Clear
• Capabilities – Reset Capabilities
• Clear customer sessions -Clear
• Product lookup tables – Regenerate
• Regenerate the product attributes lookup table – Regenerate

I hope this helps. If you have any other questions, please don’t hesitate to ask.

Hey @shameemreza ,

Thank you very much for your answer. Of course, I understand that none of the standard WC functionality while updating db directly won’t work. Hence, this question to find a workaround.

Just to clarify, right now, I’ve concluded that I need to update following things:

1. Update “_stock” on postmeta table
2. Update “_stock_status” on postmenta
3. Update “in_stock” on wc_product_attributes_lookup
4. Update “stock_quantity” on wc_product_meta_lookup
5. Update “stock_status” on wc_product_meta_lookup
6. INSERT/DELETE “object_id – term_taxonomy_id” on term_relationships table. term_taxonomy_id would be join terms and term_taxonomy tables where term.name is outofstock and taxonomy “product_visibility”. This is used in order to include/exclude products while calculating terms count in _wc_term_recount() (count for categories, tags, attributes etc).

And after queries are executed, I clear transients also by direct DB update, and hit couple of accessible wc functions:

wc_delete_product_transients() 
wc_recount_all_terms();
if ( ! wc_update_product_lookup_tables_is_running() ) {
wc_update_product_lookup_tables();
}

So, in total, 6 queries to handle stock quantity synchronization, which takes me total around 2-3 seconds (incl formatting, initial API call for stock quantity, and execution of things above) for 4-5k products with variations which is solid.

As far as I could tell, this does the job, but, since WC is quite big of a project, I’m not sure it handles everything correctly.

On the other glance, do you notice that I miss something else?

Really appreciate the answer.

Hi @shameemreza again,

I would like to get your opinion on this.

From what I could log using standard save() WC method, here are the breakpoints as regards to database changes:

wp_posts

UPDATE wp_posts SET post_modified = '2024-09-20 11:15:56', post_modified_gmt = '2024-09-20 11:15:56' WHERE ID = 34405
UPDATE wp_posts SET menu_order = 1 WHERE ID = 34429
UPDATE wp_posts SET menu_order = 2 WHERE ID = 34430
UPDATE wp_posts SET menu_order = 3 WHERE ID = 34431
UPDATE wp_posts SET post_modified = '2024-09-20 11:15:56', post_modified_gmt = '2024-09-20 11:15:56' WHERE ID = 34429

Generally, it just sets the timestamps, as well as menu_order when variations are present. In this case, this is irrelevant, should be as it is.

wp_options

UPDATE wp_options SET option_value = '1726830956' WHERE option_name = '_transient_product_query-transient-version'
DELETE FROM wp_options WHERE option_name = '_transient_wc_product_children_34405'
DELETE FROM wp_options WHERE option_name = '_transient_timeout_wc_product_children_34405'
UPDATE wp_options SET option_value = '1726830956' WHERE option_name = '_transient_product-transient-version'
INSERT INTO wp_options (option_name, option_value, autoload) VALUES ('_transient_timeout_wc_product_children_34405', '1729422956', 'off') ON DUPLICATE KEY UPDATE option_name = VALUES(option_name), option_value = VALUES(option_value), autoload = VALUES(autoload)
INSERT INTO wp_options (option_name, option_value, autoload) VALUES ('_transient_wc_product_children_34405', 'a:2:{s:3:\"all\";a:3:{i:0;i:34429;i:1;i:34430;i:2;i:34431;}s:7:\"visible\";a:2:{i:0;i:34430;i:1;i:34431;}}', 'off') ON DUPLICATE KEY UPDATE option_name = VALUES(option_name), option_value = VALUES(option_value), autoload = VALUES(autoload)

All of the changes related to wp_options table are in regards to transients. Anyway, these are all cleared on each mass update.

wp_postmeta

UPDATE wp_postmeta SET meta_value = '0' WHERE post_id = 34429 AND meta_key = '_stock'
UPDATE wp_postmeta SET meta_value = 'outofstock' WHERE post_id = 34429 AND meta_key = '_stock_status'
UPDATE wp_postmeta SET meta_value = '0' WHERE post_id = 34429 AND meta_key = '_percentage_discount'
INSERT INTO wp_postmeta (post_id, meta_key, meta_value) VALUES (34405, '_price', '24.9')

This is in fact the table that holds the actual stock and stock status. What is important here is to note that each product and parent product of variations must have “instock”/”outofstock”. As for the managing quantity, I noticed that managing parent quantity is irrelevant (correct me if I’m wrong). The _price is also irrelevant in this case.

wp_term_relationships

INSERT INTO wp_term_relationships (object_id, term_taxonomy_id) VALUES (34429, 9)

This table holds the term_taxonomy_id – which references outofstock products in combination with “product_visibility” on term_taxonomy table. If the stock is greater than 0, then this row should be deleted.

wp_termmeta

UPDATE wp_termmeta SET meta_value = '17' WHERE term_id = 300 AND meta_key = 'product_count_product_cat'
UPDATE wp_termmeta SET meta_value = '33' WHERE term_id = 301 AND meta_key = 'product_count_product_cat'
UPDATE wp_termmeta SET meta_value = '25' WHERE term_id = 71 AND meta_key = 'product_count_product_cat'
UPDATE wp_termmeta SET meta_value = '17' WHERE term_id = 300 AND meta_key = 'product_count_product_cat'
UPDATE wp_termmeta SET meta_value = '33' WHERE term_id = 301 AND meta_key = 'product_count_product_cat'
UPDATE wp_termmeta SET meta_value = '25' WHERE term_id = 71 AND meta_key = 'product_count_product_cat'

wp_term_taxonomy

UPDATE wp_term_taxonomy SET count = 1922 WHERE term_taxonomy_id = 9

For some reason, it updates wp_termmeta twice. Anyway, this will be done later on calling wc_recount_all_terms() function.

wp_stock_log

INSERT INTO wp_stock_log ( date_created, product_id, qty ) VALUES ( '2024-09-20 11:15:56', 34429, 0 )

Optional, but still notable

wp_wc_product_meta_lookup

REPLACE INTO wp_wc_product_meta_lookup (product_id, sku, virtual, downloadable, min_price, max_price, onsale, stock_quantity, stock_status, rating_count, average_rating, total_sales, tax_status, tax_class, global_unique_id) VALUES ('34429', '', '0', '0', '24.9', '24.9', '0', '0', 'outofstock', '0', '0', '0', 'taxable', 'parent', '')

REPLACE INTO wp_wc_product_meta_lookup (product_id, sku, virtual, downloadable, min_price, max_price, onsale, stock_quantity, stock_status, rating_count, average_rating, total_sales, tax_status, tax_class, global_unique_id) VALUES ('34405', '105064', '0', '0', '24.9', '24.9', '0', NULL, 'instock', '0', '0', '0', 'taxable', '', '')

In order to recalculate lookup tables, we’d have to fill this data. REPLACE INTO, ofc, uses primary key in order to match the data and update/create it accordingly. 2 queries are for variation and it’s parent post.

Hope this can be of help to someone else.

Hello guev4ra,

Thank you for your reply

I understand that updating stock for thousands of products can be time-consuming, especially with a large store. However, I would advise against making direct changes to the database.

For large stores, a better approach would be to utilize the Import/Export feature in WooCommerce.
This method is much faster and it ensures that all related fields and caches are updated properly without risking data inconsistency.

Does your ERP system allow for exporting stock data in CSV format? If so, I recommend using Import Export Suite for WooCommerce, it provides a comprehensive solution to manage large amounts of product data, including stock updates.

This plugin supports scheduled imports and can work efficiently for syncing product data from an external ERP system. It’s a more reliable and WooCommerce-compatible method for managing stock updates at scale.

Let me know if you any questions. ??

Best regards.