1

So, somehow after many migrations i ended up with thousands of featured images that are unattached. They exist in the media library and are used as featured images of the articles but they are not attached to those articles. In other words their post_parent = 0.

I need a way to reattach those images to the posts they are set as featured image.

So for each image with post_parent = 0, i have to find the post_id of were they are featured and update post_parent of the attachment to match the post ID.

I'm not a programmer, i understand what needs to be done but i don't know how to code it. If anyone can help with an example it'll be great. thanks!

1 Answer 1

4

Interesting task to solve this SQL only! So I found the following way and it worked in my local instance with testing data. Try it out and have a backup ready before running it for your complete site.

UPDATE wp_posts AS p
INNER JOIN (
  SELECT p.ID AS attachment_id, pm.post_id AS post_id
  FROM wp_posts p
  JOIN wp_postmeta pm
  ON pm.meta_value = p.ID
  WHERE (
    pm.meta_key = '_thumbnail_id'
    AND
    p.post_type = 'attachment'
    AND
    p.post_parent = 0
  )
) AS b ON p.ID = b.attachment_id
SET p.post_parent = b.post_id

What this does is the following: (images are posts)

  • SELECT all posts that are of type attachment and have a parent 0
  • all those posts should also be related to another post via the key _thubmnail_id
  • UPDATE the posts, set post_parent to what it got from the _thumbnail_id relation

Thanks to this answer, for getting the sub-query into the UPDATE.

2
  • 1
    Pure insanity!!! Seems to have immediately solved everything! I'm so glad you answered it could have taken months to do by hand. note to self: learn sql Aug 2, 2017 at 14:26
  • 1
    Glad I could help. I actually enjoy writing small scripts like this from time to time.
    – kero
    Aug 2, 2017 at 14:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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