At work, we got ourselves a nice WooCommerce shop. Using WP All Import, it automagically imports the products and updates stock. But somehow in we ended up with products in our database with no SKU.
Removing one of those products, and then rerun the import, seemed to work. So maybe these products where in the product feed once without a SKU. Who knows. Anyways, I wanted them gone, and didn’t want to delete the entire database, afraid of what Google may think of that.
So I checked the database a bit and came up with a SQL query. First, to check if you have products with empty SKU’s as well, you could run something like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT p.ID, p.post_title, p.post_type, pm.meta_value FROM wp_posts p LEFT JOIN wp_postmeta pm ON pm.post_id = p.ID AND pm.meta_key = '_sku' WHERE p.post_type = 'product' AND (pm.meta_value = '' OR pm.meta_value IS NULL) |
To remove the products, I think this should do. Of course this wouldn’t delete the files in your upload folder. If you want to delete those as well, you might want to write a script that selects the records, and then removes the images as well.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
DELETE wp_posts, wp_postmeta, wp_comments, wp_commentmeta FROM wp_posts LEFT JOIN wp_postmeta ON wp_postmeta.post_id = wp_posts.ID AND wp_postmeta.meta_key = '_sku' LEFT JOIN wp_comments ON wp_comments.comment_post_ID = wp_posts.ID LEFT JOIN wp_commentmeta ON wp_commentmeta.comment_id = wp_comments.comment_ID LEFT JOIN wp_postmeta wpm2 ON wpm2.post_id = wp_posts.ID WHERE wp_posts.post_type = 'product' AND (wp_postmeta.meta_value= '' OR wp_postmeta.meta_value IS NULL) |
This saved me so much time, thanks a million!
i have the same issue with wp all import plugin, and this help us! thanks!!!!
i need to delete more than10000 product.
how about duplicated SKU? how to remove that from woocommerce . the one i have hangs a long time.
I don’t have a running WooCommerce anymore. But if I were you I’d first query the suplicate SKU’s. See how many you get. You could then also create a delete query from that, using the id’s with some creative copy & pasting.