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

Issue

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.

Solution

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.

Answered By - Ali Ali

Answer Checked By - Marie Seifert (PHPFixing Admin)