Here are two handy SQL commands that are probably most helpful if you are doing bulk imports. I am using WP All Import, and sometimes I find that my Layered Nav disappears after an import. The only fix I have found is to remove all attributes (!) and then run the import again.
Deleting attributes one at a time is a pain if you have ten.
It’s always a good idea to take a backup of your database before you run these kinds of commands. If you don’t know how to do that, you probably shouldn’t attempt this.
NB: This will not remove any images that were uploaded into Media Library in the process of creating products (post_type = attachment).
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
Google Analytics reckons this is the most popular page on my website. If I saved you some time, please let me know in the comments below!
I like the idea of this. I’m a WP All Import user myself. Just wondering if this also get rid of all product images? Thanks!
Dave: No, images are not stored in the database and cannot be deleted with this SQL.
If there is image data that when stored in the database, if I delete the images via ftp, there would still be “junk” in the database. How can you do in this case
Check out the plugin called Image CleanUp. It might be what you’re looking for.
If you’re using WPAllImport and these commands, do make sure you are also using the option for WPAllImport to check for media files in the media library before importing images.
It’s pretty tricky to bulk delete images in WordPress. I’d suggest that next time you import images, you give them all a filename with identical prefix, e.g. wc-###-filename.jpg (### = import batch) etc, to make it easy to search for them in your library. Programs like Irfanview are great for batch renaming.
Yes you did save us a ton of time.. no need to go to import plugin support.. this did the trick. Thank you again :)
Fantastic post saved me tons of time on my imports and cleanups. Thanks for this!!
This saved me quite a bit of time and hassle…in fact making what I was trying to do possible.
I had done a Cart 2 Cart migration from BigCommerce to WooCommerce. The way that BigCommerce does attributes (way too complicated) translates into a mess in WooCommerce, so I needed to nuke all the product attributes and variations.
Your “Remove all attributes from WooCommerce” query did the trick. Thanks!
This was helpful. Thanks a lot.
I just had to do additional delete queries after this to support latest WooCommerce version. Latest WC stores attributes at another custom table ‘woocommerce_attribute_taxonomies’.
It will be great if you can add that part in your article to make it complete for latest WC.
Thanks Mansi! I will look into it.
Awesome , thanks for this Melissa! I’m a wordpress newbie but i’m gonna back up and run this anyway as I have 120 products to deal with. If you could share the additional delete queries to support the latest WC update as per Mansi’s post, that would be so great!
Hi Rexx, I’ve been pretty busy, but I should have a chance to look at this next week.
Great, Thanks Melissa!
This is great if you’re removing ALL products – but I searched everywhere to find somewhere to help me delete products based on a list of SKUs given to me by a shop manager. In the end I’ve written some simple instructions using SQL to convert SKU’s to post_ids then clean up the post table and post_meta tables. I’ve shared it here; https://kristianbrown.co.uk/delete-woocommerce-products-using-skus/
Ah, if you find that the attributes are still showing after you have used Melissa’s query, then you should delete the WooCommerce transient cache, attribute order and attribute taxonomies (WooCommerce 4.8.x):
“`
DELETE FROM wp_woocommerce_attribute_taxonomies;
DELETE FROM wp_termmeta WHERE meta_key like ‘order\_pa\_%’;
DELETE FROM `wp_options` WHERE `option_name` LIKE (‘\_transient\_wc\_%’);
“`