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

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

mustafakucuk commented Sep 29, 2019

Thanks (:

@veselect
Copy link

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

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
Copy link

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
Copy link

roshansamasi commented Aug 4, 2020

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

@BeshoyAtef
Copy link

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
Copy link

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

nick-knoops commented Jan 19, 2021

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

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