Remove all WooCommerce products from database via SQL

delete-all-woocommerce-products.php

<?php

require dirname(__FILE__).'/wp-blog-header.php';

$wpdb->query("DELETE FROM wp_terms WHERE term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE taxonomy LIKE 'pa_%')");
$wpdb->query("DELETE FROM wp_term_taxonomy WHERE taxonomy LIKE 'pa_%'");
$wpdb->query("DELETE FROM wp_term_relationships WHERE term_taxonomy_id not IN (SELECT term_taxonomy_id FROM wp_term_taxonomy)");
$wpdb->query("DELETE FROM wp_term_relationships WHERE object_id IN (SELECT ID FROM wp_posts WHERE post_type IN ('product','product_variation'))");
$wpdb->query("DELETE FROM wp_postmeta WHERE post_id IN (SELECT ID FROM wp_posts WHERE post_type IN ('product','product_variation'))");
$wpdb->query("DELETE FROM wp_posts WHERE post_type IN ('product','product_variation')");
$wpdb->query("DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL");

delete-all-woocommerce-products.sql

-- Remove all 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
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

Thanks!
If you encounter something like Lock wait timeout exceeded; try restarting transaction just execute rows from SQL script one by one.

remove all products and relations

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';

Remove all products, categories, meta and relations

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';

DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL;

delete from `wp_termmeta`
where 
	`term_id` in ( 
		SELECT `term_id`
		FROM `wp_term_taxonomy`
		WHERE `taxonomy` in ('product_cat', 'product_type', 'product_visibility') 
	);

delete from `wp_terms`  
where 
	`term_id` in ( 
		SELECT `term_id`
		FROM `wp_term_taxonomy`
		WHERE `taxonomy` in ('product_cat', 'product_type', 'product_visibility') 
	);
	
DELETE FROM `wp_term_taxonomy` WHERE `taxonomy` in ('product_cat', 'product_type', 'product_visibility');

DELETE meta FROM wp_termmeta meta LEFT JOIN wp_terms terms ON terms.term_id = meta.term_id WHERE terms.term_id IS NULL;

DELETE FROM wp_woocommerce_attribute_taxonomies;

DELETE FROM wp_woocommerce_sessions;

I want to find a way hot to remove all out of stock products in database. I found below code

DELETE p FROM wp_posts p join wp_postmeta pm on p.ID = pm.post_id WHERE p.post_type = 'product' and pm.meta_key='_stock_status' and pm.meta_value='outofstock';

but it shows Unexpected keyword near join.

By the way i'm using mariadb 10

Thank you so much!

Hello @ruscon,
why I get the error "Unexpected keyworkd (near LEFT JOIN)" in the row "DELETE meta FROM wp_termmeta meta LEFT JOIN wp_terms terms ON terms.term_id = meta.term_id WHERE terms.term_id IS NULL;"?

Thanks

@allampatu, @ruscon
I have to insert an empty serialzed array in the row with value "_transient_wc_attribute_taxonomie" from wp_options, otherwise the woocommerce attribute-section complains and import of products with attribute doesn't work.

UPDATE wp_options SET option_value='a:0:{}' WHERE option_name = '_transient_wc_attribute_taxonomies';

Regards, Thomas

This plugin also handles doing this for you:
https://wordpress.org/plugins/woo-product-remover/

Code:
https://plugins.trac.wordpress.org/browser/woo-product-remover/trunk/woo-product-remover.php

Thanks (:

This plugin also handles doing this for you:
https://wordpress.org/plugins/woo-product-remover/

Code:
https://plugins.trac.wordpress.org/browser/woo-product-remover/trunk/woo-product-remover.php

None of this methods or this plugin will remove the image post relation from database

This plugin also handles doing this for you:
https://wordpress.org/plugins/woo-product-remover/

Code:
https://plugins.trac.wordpress.org/browser/woo-product-remover/trunk/woo-product-remover.php

Hasn't been updated for three years...wouldn't recommend using.

Thanks that was really helpful
I used this to also delete all products tags

DELETE FROM wp_terms WHERE term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE taxonomy = 'product_tag'); DELETE FROM wp_term_taxonomy WHERE taxonomy = 'product_tag'; DELETE FROM wp_term_relationships WHERE term_taxonomy_id not IN (SELECT term_taxonomy_id FROM wp_term_taxonomy);

Thanks for the info. Any ideas on how to save previous orders since the order is attached to the product post id? We need to preserve previous order data.

Hi Can you please share how can i delete woocommerce products without images using mysql

Hi Can you please share how can i delete woocommerce products without images using mysql

DELETE p FROM wp_posts p join wp_postmeta pm on p.ID = pm.post_id WHERE p.post_type = 'product' ';

This will delete all products and its meta without the media

Hi Thanks for your response. What i actually want to do is, in my store i have some products with images and some products with the image link. I would like to delete all products which do not have an image.

Thanks a lot! @ruscon

I have added a custom taxonomy product_brand to the list.

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';

DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL;

delete from `wp_termmeta`
where 
	`term_id` in ( 
		SELECT `term_id`
		FROM `wp_term_taxonomy`
		WHERE `taxonomy` in ('product_cat', 'product_type', 'product_visibility', 'product_brand') 
	);

delete from `wp_terms`  
where 
	`term_id` in ( 
		SELECT `term_id`
		FROM `wp_term_taxonomy`
		WHERE `taxonomy` in ('product_cat', 'product_type', 'product_visibility', 'product_brand') 
	);
	
DELETE FROM `wp_term_taxonomy` WHERE `taxonomy` in ('product_cat', 'product_type', 'product_visibility', 'product_brand');

DELETE meta FROM wp_termmeta meta LEFT JOIN wp_terms terms ON terms.term_id = meta.term_id WHERE terms.term_id IS NULL;

DELETE FROM wp_woocommerce_attribute_taxonomies;

DELETE FROM wp_woocommerce_sessions;