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'
@kilbot
Copy link

kilbot commented Jun 4, 2014

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?

@logicbird
Copy link

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?

@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