Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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

This comment has been minimized.

Copy link

@jjelev 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

This comment has been minimized.

Copy link

@ruscon 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

This comment has been minimized.

Copy link

@ruscon 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

This comment has been minimized.

Copy link

@alfirus 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

This comment has been minimized.

Copy link

@enri18 enri18 commented Feb 19, 2018

Thank you so much!

@allampatu

This comment has been minimized.

Copy link

@allampatu allampatu commented Mar 5, 2018

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

This comment has been minimized.

Copy link

@thonegh 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

@tripflex

This comment has been minimized.

@mustafakucuk

This comment has been minimized.

Copy link

@mustafakucuk mustafakucuk commented Sep 29, 2019

Thanks (:

@veselect

This comment has been minimized.

Copy link

@veselect veselect commented Dec 16, 2019

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

This comment has been minimized.

Copy link

@lkeels 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

This comment has been minimized.

Copy link

@BeshoyAtef BeshoyAtef commented May 6, 2020

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

This comment has been minimized.

Copy link

@matthewodbt matthewodbt commented May 20, 2020

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

This comment has been minimized.

Copy link

@roshansamasi roshansamasi commented Aug 4, 2020

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

@BeshoyAtef

This comment has been minimized.

Copy link

@BeshoyAtef BeshoyAtef commented Aug 5, 2020

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

This comment has been minimized.

Copy link

@roshansamasi roshansamasi commented Aug 5, 2020

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

This comment has been minimized.

Copy link

@simongcc 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.