How to Completely Delete or Reset WooCommerce Products and Data

If you’ve been working with WooCommerce and need to start over — for example, after importing thousands of products that didn’t go as planned — manually deleting products through the admin interface can be painfully slow and inefficient.

In this guide, I’ll show you how to completely wipe all WooCommerce product data, including:

  • Products and product variations
  • Product attributes (like sizes, colours, etc.)
  • Associated metadata
  • Taxonomy relationships
  • Orphaned postmeta

All using safe and tested SQL queries, compatible with the latest version of WooCommerce.

Before You Start

  • Backup your database — seriously. These queries are destructive and permanent.
  • This guide assumes you’re comfortable running SQL queries, either via phpMyAdmin, Adminer, WP-CLI, or a database management tool like TablePlus, Sequel Pro, or MySQL CLI.
  • These queries are designed for the default WordPress table prefix wp_. If you use a custom prefix (e.g., wp23_), update all table names accordingly.

What This Will Delete

This reset process will:

  • Delete all product and product_variation posts
  • Delete all associated postmeta and orphaned postmeta
  • Delete product attributes (taxonomies starting with pa_)
  • Remove term relationships and taxonomies related to WooCommerce products

The SQL Queries Explained

Here’s the complete set of SQL queries used to reset WooCommerce products and related data:

-- 1. Delete all products and variations
DELETE FROM wp_posts 
WHERE post_type = 'product' OR post_type = 'product_variation';

-- 2. Delete orphaned postmeta
DELETE pm  
FROM wp_postmeta pm 
LEFT JOIN wp_posts p ON pm.post_id = p.ID 
WHERE p.ID IS NULL;

-- 3. Delete all custom product attributes (pa_*)
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_%';

-- 4. Clean up term relationships
DELETE FROM wp_term_relationships 
WHERE term_taxonomy_id NOT IN (
  SELECT term_taxonomy_id 
  FROM wp_term_taxonomy
);

DELETE FROM wp_term_relationships 
WHERE object_id IN (
  SELECT ID 
  FROM wp_posts 
  WHERE post_type IN ('product', 'product_variation')
);

-- 5. Redundant cleanup: make sure no product postmeta is left
DELETE FROM wp_postmeta 
WHERE post_id IN (
  SELECT ID 
  FROM wp_posts 
  WHERE post_type IN ('product', 'product_variation')
);

-- 6. Final cleanup of any orphaned postmeta
DELETE pm
FROM wp_postmeta pm
LEFT JOIN wp_posts wp ON wp.ID = pm.post_id
WHERE wp.ID IS NULL;

How to Run the SQL Queries

You can run these SQL queries using one of the following methods:

Option 1: phpMyAdmin

  1. Log into your hosting control panel (e.g., cPanel).
  2. Open phpMyAdmin.
  3. Select your WordPress database.
  4. Go to the SQL tab.
  5. Paste and run the queries.

Option 2: WP-CLI (Advanced)

If you’re using WP-CLI, you can run raw SQL using:

wp db query < reset-products.sql

Where reset-products.sql contains the queries listed above.

Option 3: Custom Plugin or Script

You could run these queries using $wpdb in a custom admin plugin or mu-plugin, though direct SQL execution via GUI tools is safer for one-off operations like this.

What’s Left Intact

This process does not touch:

  • Orders or order data
  • Customers and user accounts
  • WooCommerce settings
  • Product categories and tags (unless they’re only associated with deleted products)
  • Downloadable files or attachments unless manually handled

If you’d also like to remove product categories, tags, or media attachments, let me know and I can provide additional clean-up queries.

Optional: Reset WooCommerce Completely

If you want to wipe all WooCommerce data, including orders, coupons, settings, etc., you can use the official method, define this constant in wp-config.php and delete the plugin:

define( 'WC_REMOVE_ALL_DATA', true );

Then deactivate and delete the WooCommerce plugin — it will clean up everything, including tables and options.

Bonus

Here’s the extended SQL query set that will fully reset WooCommerce, including:

  • ✅ Products & Variations (already covered)
  • ✅ Orders
  • ✅ Coupons
  • ✅ Customer data
  • ✅ Product attributes & taxonomies
  • ✅ Term relationships
  • ✅ Orphaned postmeta and termmeta
  • ✅ Media attachments (optional)
  • ✅ WooCommerce logs, notes, sessions

⚠️ Again: Backup your database before running this! This is irreversible and designed for a full WooCommerce reset — not just product clean-up.

Full WooCommerce Reset SQL Queries

🧠 Table prefix assumed to be wp_. Replace it with your own if different.

-- ================================
-- STEP 1: DELETE ALL PRODUCTS + VARIANTS
-- ================================
DELETE FROM wp_posts WHERE post_type IN ('product', 'product_variation');

-- Delete orphaned product meta
DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts p ON pm.post_id = p.ID WHERE p.ID IS NULL;

-- ================================
-- STEP 2: DELETE PRODUCT ATTRIBUTES
-- ================================
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_%';

-- ================================
-- STEP 3: DELETE TERM RELATIONSHIPS
-- ================================
DELETE FROM wp_term_relationships 
WHERE object_id IN (
  SELECT ID 
  FROM wp_posts 
  WHERE post_type IN ('product','product_variation')
);
DELETE FROM wp_term_relationships 
WHERE term_taxonomy_id NOT IN (
  SELECT term_taxonomy_id FROM wp_term_taxonomy
);

-- ================================
-- STEP 4: DELETE ORDERS + ORDER ITEMS
-- ================================
DELETE FROM wp_posts WHERE post_type = 'shop_order';
DELETE FROM wp_posts WHERE post_type = 'shop_order_refund';

DELETE FROM wp_postmeta 
WHERE post_id NOT IN (
  SELECT ID FROM wp_posts
);

DELETE FROM wp_woocommerce_order_items;
DELETE FROM wp_woocommerce_order_itemmeta;

-- ================================
-- STEP 5: DELETE COUPONS
-- ================================
DELETE FROM wp_posts WHERE post_type = 'shop_coupon';

-- ================================
-- STEP 6: DELETE CUSTOMER SESSIONS, NOTES, LOGS
-- ================================
DELETE FROM wp_wc_customer_lookup;
DELETE FROM wp_wc_order_stats;
DELETE FROM wp_wc_order_product_lookup;
DELETE FROM wp_wc_order_tax_lookup;
DELETE FROM wp_wc_order_coupon_lookup;
DELETE FROM wp_wc_download_log;
DELETE FROM wp_wc_webhooks;
DELETE FROM wp_comments WHERE comment_type IN ('order_note', 'review');
DELETE FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_ID FROM wp_comments);

-- ================================
-- STEP 7: DELETE MEDIA ATTACHMENTS (OPTIONAL)
-- ================================
-- Only run this if you want to delete all product/media files
DELETE FROM wp_posts WHERE post_type = 'attachment';

-- Remove orphaned postmeta again
DELETE pm
FROM wp_postmeta pm
LEFT JOIN wp_posts wp ON wp.ID = pm.post_id
WHERE wp.ID IS NULL;

-- ================================
-- STEP 8: DELETE OPTIONS (OPTIONAL WooCommerce Settings Reset)
-- ================================
DELETE FROM wp_options WHERE option_name LIKE '%woocommerce_%';

✅ Summary: What This Deletes

Data TypeDeleted?
Products & Variations✅ Yes
Product Metadata✅ Yes
Product Attributes✅ Yes
Product Categories❌ No
Product Tags❌ No
Orders & Refunds✅ Yes
Order Items & Metadata✅ Yes
Customers (wc_customer_lookup)✅ Yes
Reviews / Notes✅ Yes
Coupons✅ Yes
Logs & Webhooks✅ Yes
Media Attachments✅ Yes (optional)
WooCommerce Settings✅ Yes (optional)

on in Blog, WordPress | Last modified on

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *