WooCommerce – Delete all products in a category with SQL

Learn how to bulk delete WooCommerce products and product variations by category using SQL for blazing speeds.

This SQL approach is way faster than any plugin and is a good option if you don’t have direct access to your server and WP-CLI, and where doing it the regular admin dashboard way is out of the question due to the number of products.

These below queries were tried on a WooCommerce website set up in shared hosting. Around 10 000 products were deleted with 27 variations each. That’s actually 270 000 products!

Note that the category itself won’t be deleted. This is easily done in the WordPress admin dashboard AFTER the below queries have been executed.

Don’t forget to back up your database first, when doing stuff like this!

# Step 1
# Deletes all rows from the wp_postmeta table which contains product metadata.
# This one might take a while depending on your number of products.
# Make sure you swap out 23 to your category ID.
DELETE FROM wp_postmeta
WHERE post_id IN (
	SELECT ID FROM wp_posts
	WHERE wp_posts.ID
	IN (
		SELECT object_id
		FROM wp_term_relationships
		WHERE term_taxonomy_id = 23
	) OR wp_posts.post_parent IN (
		SELECT object_id
		FROM wp_term_relationships
		WHERE term_taxonomy_id = 23
	)
);

# Step 2
# Deletes the products themselves from the
# wp_posts table which contains main product info.
# Make sure you swap out 23 to your category ID.
DELETE FROM wp_posts
WHERE wp_posts.ID
IN (
	SELECT object_id
	FROM wp_term_relationships
	WHERE term_taxonomy_id = 23
) OR wp_posts.post_parent IN (
	SELECT object_id
	FROM wp_term_relationships
	WHERE term_taxonomy_id = 23
);

# Step 3
# Deletes the products relationships which are no longer needed,
# from the wp_term_relationships table.
# Make sure you swap out 23 to your category ID.
DELETE FROM wp_term_relationships
WHERE term_taxonomy_id = 23;