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

How to delete woocommerce products that belongs to specific category from the database

I need a MySql query statement to delete all woocommerce products that belong to a category or do not belong to another, similar to this:

DELETE FROM wp_postmeta WHERE post_id IN (SELECT ID
FROM wp_posts
LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
WHERE wp_term_taxonomy.term_id NOT IN (29,31,32,33,34) AND post_type IN ('product','product_variation')
GROUP BY wp_posts.ID);

And I am dealing with a large amount of data, more than 100,000 products.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    In case anyone is having the same need. This is how I solved the deletion:

    1- The most important thing is to make sure that the categories don't overlap with each other, like the products that you are trying to delete doesn't have other categories attached to them. This can be done manually (if you have few products) or with a query to de-attach the unneeded relationships.

    2- Next I will advise deleting products that belong to only one category per time, the query should be specific to prevent any undesired results and conflicts.

    3- Select the products before deleting to see if there's any conflict with this query:

    SELECT * FROM wp_posts WHERE ID IN(SELECT posts.object_id from (SELECT object_id FROM wp_term_relationships
LEFT JOIN wp_posts ON (wp_posts.ID = wp_term_relationships.object_id)
WHERE term_taxonomy_id = 38 AND post_type IN ('product','product_variation')
GROUP BY object_id) as posts);

    4- Delete only one wanted category ( BY ID) products per time:

    DELETE FROM wp_posts WHERE ID IN(SELECT posts.object_id from (SELECT object_id FROM wp_term_relationships
LEFT JOIN wp_posts ON (wp_posts.ID = wp_term_relationships.object_id)
WHERE term_taxonomy_id = 38 AND post_type IN ('product','product_variation')
GROUP BY object_id) as posts);

    The Above Query Took 1 SECOND in execution for more than 30,000 products per category.


  2. 0

    The accepted answer deletes the products but leaves the meta data.

    DELETE FROM wp_postmeta WHERE post_id IN( SELECT id FROM wp_posts WHERE ID IN(SELECT posts.object_id from (SELECT object_id FROM wp_term_relationships
LEFT JOIN wp_posts ON (wp_posts.ID = wp_term_relationships.object_id)
WHERE term_taxonomy_id = 38 AND post_type IN ('product','product_variation')
GROUP BY object_id) as posts));

    Executing this SQL query before the delete query will delete the meta data of products of the category. After this the query to delete the products (4th step of selected answer) can be executed.

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

Back To Top
Search