How to find and delete orphaned product variations from WooCommerce sites.

delete.sql

DELETE o FROM `wp_posts` o
LEFT OUTER JOIN `wp_posts` r
ON o.post_parent = r.ID
WHERE r.id IS null AND o.post_type = 'product_variation'

select.sql

SELECT * FROM `wp_posts` o
LEFT OUTER JOIN `wp_posts` r
ON o.post_parent = r.ID
WHERE r.id IS null AND o.post_type = 'product_variation'

Hi Patrick, thanks so much for these snippets, they have come in very handy when troubleshooting problems with product_variations. I'm wondering, do you know if orphaned variations are common, or was this just a rogue version of WooCommerce which created the orphans?

This is really useful. Thank you. Yours is the only code I have seen to address the problem of orphaned variations. I have found that I am the the proud care taker of over 600 orphans myself thanks to your SELECT statement. I first noticed them as unexpected results in a php script that I wrote to simply list each product variation and its sku. Also realized they are the cause of a Dashboard problem. They are showing up in woocommerce>products when I search for certain products. They show up as blank results in the list with only a published date being displayed for each one. Inspecting the HTML I can see that these are in fact the orphans. So I can use your DELETE statment to remove them. Great! But what about all the records that will leave in wp_postmeta? Don't we have a lot of Meta Orphans to clean up too after running your query?

i noticed the delete statement is..

DELETE o

and the select..

SELECT *

is this correct? if so could you explain why this is?
cheers

This worked very well for me. The orphaned variations no longer show up in the Out of Stock Reports.

How do we make sure the orphaned variations are cleaned out by woocommerce completely?

Tnx, it saved me much time

thanks. it worked for me too

Old post but it's the one I found. As an update to this, I believe you should also delete the associated postmeta rows. here's the query I used:

delete v, pm
from wp_posts v
left join wp_postmeta pm on (pm.post_id = v.id)
left join wp_posts p on (v.post_parent = p.id)
where v.post_type = 'product_variation' and p.id is null

Now we have a built in WooCommerce option for this as described here: https://stackoverflow.com/a/46245440/1788684

WP Admin > Woocommerce > Status > Tools tab > Delete Orphaned variations