2

I am using woocommerce as a shopping cart on a wordpress site. Products and Product Variations are each stored as records in the wp_posts table. Each of these Variations have aprox. 15-20 associated records in wp_postmeta (to store various details about that product, such as SKU or price).

What I've discovered is that many variations (over 600 in this case) do not have an associated parent product. Well, they do but that Parent Product no longer exists. I have found the following to remove these orphaned variations from wp_posts:

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'

I think this will work well for that purpose. But it would still leave many records in wp_postmeta table.

My question is, can I remove any record from wp_postmeta whose post_id does not correspond to an existing record in wp_posts. I mean, I know I can do it, but is there any potential pitfall of removing those orphaned records from wp_postmeta. That is, if meta info corresponds to a product variation that does not exist. can I safely delete it?

  • So these are posts that have a parent post, but the ID of the parent post is for a post that no longer exists? – Tom J Nowell Jun 9 '15 at 0:48
  • I am dealing with the same issue now. It will be good to know what you did about this problem finally. – r3st0r3 Nov 15 '17 at 5:52
1

A different way of doing this (not better, but easier to follow) would be:

DELETE * FROM wp_postmeta WHERE post_id NOT IN (SELECT ID FROM wp_posts)

Obviously changing the prefixes from 'wp_' to whatever your setup uses

  • This doesn't answer the question. – Isaac Lubow Sep 27 '18 at 17:37
-1

You just need to do a RIGHT JOIN to query the rows that have NULL values

  • Can you please add more information to this answer. As is, it isn't much use to anyone who doesn't already know how what they are doing. – s_ha_dum Jun 8 '15 at 22:07
  • If these changes to the query are made, and it succesfully removes all the post records, it still doesn't answer the question is it safe to do? Or will running said query break logicbirds website? – Tom J Nowell Jun 9 '15 at 0:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.