CODEWITHSUNDEEP
phpmysqlsqlwordpress
Jonathan Hutchinson
Jonathan Hutchinson

Reputation: 159

Remove Woocommerce products but keep categories

I have a Wordpress site which runs using the Woocommerce plugin. I have a large number of products that I need to remove, unfortunately doing this through the Wordpress backend is a very time consuming and frustration process.

I have been looking for a way to remove the products through phpMyAdmin and have found the following

DELETE relations.*, taxes.*, terms.*
FROM wp_term_relationships AS relations
INNER JOIN wp_term_taxonomy AS taxes
ON relations.term_taxonomy_id=taxes.term_taxonomy_id
INNER JOIN wp_terms AS terms
ON taxes.term_id=terms.term_id
WHERE object_id IN (SELECT ID FROM wp_posts WHERE post_type='product');

DELETE FROM wp_postmeta WHERE post_id IN (SELECT ID FROM wp_posts WHERE post_type = 'product');
DELETE FROM wp_posts WHERE post_type = 'product';

This works great, accept it also removes the product categories which I would like to keep.

Is there a way to remove all woocommerce products but keep the categories using SQL?

Thanks

Upvotes: 1

Views: 2780

Answers (1)

Ruslan Novikov
Ruslan Novikov

Reputation: 1527

In my case, this MySQL query helped

-- Remove all product attributes from WooCommerce
DELETE FROM wp_terms WHERE term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE taxonomy LIKE 'pa_%');
DELETE FROM wp_term_taxonomy WHERE taxonomy LIKE 'pa_%';
DELETE FROM wp_term_relationships WHERE term_taxonomy_id not IN (SELECT term_taxonomy_id FROM wp_term_taxonomy);

-- Delete all WooCommerce products and product variation
DELETE FROM wp_term_relationships WHERE object_id IN (SELECT ID FROM wp_posts WHERE post_type IN ('product','product_variation'));
DELETE FROM wp_postmeta WHERE post_id IN (SELECT ID FROM wp_posts WHERE post_type IN ('product','product_variation'));
DELETE FROM wp_posts WHERE post_type IN ('product','product_variation');

-- Delete orphaned postmeta
DELETE pm FROM wp_postmeta pm
LEFT JOIN wp_posts wp ON wp.ID = pm.post_id
WHERE wp.ID IS NULL

Taken from github.com

Upvotes: 1

Related Questions