Skip to content

Instantly share code, notes, and snippets.

@mikaelz
Last active March 15, 2024 12:53
Show Gist options
  • Save mikaelz/d574457cb22f1f79f337 to your computer and use it in GitHub Desktop.
Save mikaelz/d574457cb22f1f79f337 to your computer and use it in GitHub Desktop.
Remove all WooCommerce products from database via SQL
<?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");
-- 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
@thonegh
Copy link

thonegh commented Mar 29, 2018

@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

@mustafakucuk
Copy link

Thanks (:

@veselect
Copy link

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

@lkeels
Copy link

lkeels commented Jan 2, 2020

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.

@BeshoyAtef
Copy link

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

@matthewodbt
Copy link

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.

@roshansamasi
Copy link

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

@BeshoyAtef
Copy link

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

@roshansamasi
Copy link

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.

@simongcc
Copy link

simongcc commented Sep 7, 2020

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;

@nick-knoops
Copy link

Does anyone know how to delete all products, with image data etc, before a specific date? Like the creation date of the product?

@anilviradiya
Copy link

Insert product query from CSV in Mysql for woocommerce?
Is it available/

@khoipro
Copy link

khoipro commented Nov 1, 2023

I run some queries like all of you mentioned. But ORDERS still there. Missing that :))

@BeshoyAtef
Copy link

BeshoyAtef commented Nov 1, 2023 via email

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment