Skip to content

Instantly share code, notes, and snippets.

@mikaelz
Last active September 4, 2024 13:39
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
@jjelev
Copy link

jjelev commented May 15, 2017

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

@ruscon
Copy link

ruscon commented Jun 22, 2017

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

@ruscon
Copy link

ruscon commented Jul 20, 2017

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;

@alfirus
Copy link

alfirus commented Jul 26, 2017

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

@enri18
Copy link

enri18 commented Feb 19, 2018

Thank you so much!

@allampatu
Copy link

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

@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

@shoaibmodel
Copy link

hello i need help i want to remove my all out of stock product anyone can guide me
how i can do this through any plugin or through any way?

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