attach unattached featured images to respective posts

Question

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!

Answer 1

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.