August 14, 2022 | Posted in WordPress
I have recently struggled with more than seven thousand products in the database. I needed to delete them because all the products I imported from a CSV file were not adequately formatted to work with the WooCommerce products table. So that I start to solve this problem.
My first approach was to find the database table; I found all the products not stored in a single table. Instead, product objects are stored in a different data table.
I have written a query but found a much more efficient one here. I applied this query, and all the products deleted
Here is the query:
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 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');This is the way I deleted all the WooCommerce products. You can edit the above query if you don’t need to delete all of them.
Let’s say you want to delete specific categories of products, all the categories stored in the terms fields so that you need to write conditions something like that:
WHERE terms='category_name'