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
# Credit to @ruscon
# product removal
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;
# product related meta removal
TRUNCATE `wp_wc_product_meta_lookup`;
# product order removal
TRUNCATE `wp_wc_order_product_lookup`;
# session removal
DELETE FROM wp_woocommerce_sessions;
# reset terms count
UPDATE `wp_term_taxonomy`
SET count = 0 where taxonomy in ('product_cat', 'product_type', 'product_visibility', 'product_brand');
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
# attribute removal
DELETE FROM wp_woocommerce_attribute_taxonomies;
# terms and taxonomy removal
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 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_term_taxonomy` WHERE `taxonomy` in ('product_cat', 'product_type', 'product_visibility', 'product_brand');
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')
);
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.