Skip to content

Instantly share code, notes, and snippets.

@pmgarman
Created August 1, 2013 22:35
Show Gist options
  • Save pmgarman/6135967 to your computer and use it in GitHub Desktop.
Save pmgarman/6135967 to your computer and use it in GitHub Desktop.
How to find and delete orphaned product variations from WooCommerce sites.
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 * 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'
@modul4richard
Copy link

i noticed the delete statement is..

DELETE o

and the select..

SELECT *

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

@amitramani
Copy link

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?

@mechanicalpie
Copy link

Tnx, it saved me much time

@FunkySindhi
Copy link

thanks. it worked for me too

@skillio
Copy link

skillio commented Apr 22, 2017

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

@binodkalathil
Copy link

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment