Question 260197 in Woocommerce Question posted in
The official Woocommerce documentation can be found here.

Resetting all woocommerce products stock to 0

I have around 5k products in my store and I wonder if there is a way via PhpMyAdmin or any other plugin you might know to set all my stock levels from variables products to 0 due to the fact I upload every day a new CSV.

Is it possible via PhpMyAdmin to reset the entire to 0 of every product and the stock status to ‘out of stock’ so I can import my new CSV and update only the products are in.

Tags:

2

Answers


  1. 0

    As your question is not very clear regarding the type of products that you want to alter. In the code below, the 2 SQL queries will (for all the products):

    • Set the stock quantity to 0 (zero)
    • Set the stock status to "Out of stock"

    Always before, be sure to make a backup of the database.

    The query to set the stock quantity to 0:

    UPDATE wp_postmeta pm
INNER JOIN wp_wc_product_meta_lookup pml
    ON pm.post_id =  pml.product_id
SET pm.meta_value = '0', pml.stock_quantity = '0'
WHERE pm.meta_key = '_stock';

    The query to set the stock status "Out of stock":

    UPDATE wp_postmeta pm
INNER JOIN wp_wc_product_meta_lookup pml
    ON pm.post_id =  pml.product_id
SET pm.meta_value = 'outofstock', pml.stock_status = 'outofstock'
WHERE pm.meta_key = '_stock_status';

    Tested, works.

    Note: For the stock status, it should also require to INSERT IN wp_term_relationships table all the related product IDs with the term ID corresponding to the term slug "outofstock". It will make 5000 inserts, as you have 5000 related products.

    Login or Signup to reply.
  2. 0

    I’m further elaborating on LoicTheAztec’s answer, adding the changes to wp_term_relationships.

    Step 1: Set Stock Quantity to 0.

    UPDATE wp_postmeta pm
INNER JOIN wp_wc_product_meta_lookup pml
    ON pm.post_id =  pml.product_id
SET pm.meta_value = '0', pml.stock_quantity = '0'
WHERE pm.meta_key = '_stock';

    Step 2: Set Stock Status to "Out of Stock".

    UPDATE wp_postmeta pm
INNER JOIN wp_wc_product_meta_lookup pml
    ON pm.post_id =  pml.product_id
SET pm.meta_value = 'outofstock', pml.stock_status = 'outofstock'
WHERE pm.meta_key = '_stock_status';

    Step 3: Update Term Relationships.

    WooCommerce might use terms to manage stock status. To make sure the products are marked as ‘out of stock’ in terms as well, you can run:

    INSERT IGNORE INTO wp_term_relationships (object_id, term_taxonomy_id)
SELECT pml.product_id, (SELECT term_id FROM wp_terms WHERE slug = 'outofstock')
FROM wp_wc_product_meta_lookup pml
WHERE pml.stock_status = 'outofstock';

    The INSERT IGNORE in the third SQL statement is used to skip any duplicate entries. In the wp_term_relationships table, the combination of object_id and term_taxonomy_id is usually a primary key. If a product is already marked as ‘out of stock’ (i.e., it already has a relationship with the term ‘outofstock’), attempting to insert a duplicate entry for that product would result in a ‘Duplicate entry’ error.

    After running these queries your products should all be set to a stock qty of 0 and marked as ‘out of stock’. You can then import your new CSV to update only the products that are in stock or do it in the admin panel.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search