While developing a series of websites using the great Woocommerce for WordPress the development process we’re using at kc web design kent meant we needed to make copies of existing website designs and then redevelop them into new websites. Part of the process we went through at kc web design kent was changing the design and then adding a different set of products but to do this we first have to delete the existing ones. Adding products in Woocommerce is made very easy with the CSV import suite and updating existing products using a merge CSV import but there is no easy way to delete all products, especially when there are thousands of them. A nice feature in Woocommerce would be a way to bulk delete products but until that appears in the core or until someone makes a plugin the only way to delete products on-mass is to do it directly on the database.
Working directly on the database is always a bit scary, especially if you don’t know MySQL syntax and how to write complicated queries. Because of the way Woocommerce uses posts to store product data and the fact that products can have variations and custom post types, it can make it very difficult to know where all the data is stored. Woocommerce spreads the product data across posts, terms, taxonomies, term_relationships and post meta which makes things tricky if you want to do it in a visual SQL client. The best way is to use a few SQL queries. On this recent project at kc web design kent we needed to delete a lot of products and so after a bit of searching around on Google we came up with these queries.
Before using these remember to backup your database in case anything goes wrong. You’ll also need to change the default table prefix (wp_) to whatever you have used in your database. And remember, its always good practice not to use the default wp_ prefix on any WordPress websites. The queries below also have to be run in this order.
DELETE relations.*, taxes.*, terms.*
FROM wp_term_relationships AS relations
INNER JOIN wp_term_taxonomy AS taxes
INNER JOIN wp_terms AS terms
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';