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'

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