attach unattached featured images to respective posts

by

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.