Mysql query to set a featured image on all wordpress posts without a featured image set

If you want to set a featured image on all wordpress posts that dont already have a featured image set, then this mysql query can help:

INSERT INTO 
	wp_postmeta (post_id,meta_key,meta_value)
SELECT 
    p.ID, '_thumbnail_id', id_of_image_from_upload_table
FROM
    wp_posts p
        LEFT JOIN
    wp_postmeta pm ON p.ID = pm.post_id
        AND pm.meta_key = '_thumbnail_id'
WHERE
    p.post_type IN ('post')
        AND p.post_status IN ('publish')
        AND pm.meta_key IS NULL
LIMIT 0 , 100000;

# please note that this query will fail; you have to replace ‘id_of_image_from_upload_table’ with the id of the image you uploaded.

To find that ID, upload an image to the media library, then run this query:

SELECT 
    ID
FROM
    wp_posts
WHERE
    post_type = 'attachment'
ORDER BY ID DESC
LIMIT 1;

You could join it all together and run it in a single query if you like..
Finally, update the postmeta table for entries who have a thumbnail image, but a null value:

update wp_postmeta set meta_value=id_of_image_from_upload_table where meta_key = '_thumbnail_id' and meta_value is null;

Leave a Reply