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
andproduct_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
- Log into your hosting control panel (e.g., cPanel).
- Open phpMyAdmin.
- Select your WordPress database.
- Go to the SQL tab.
- 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 Type | Deleted? |
---|---|
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) |