To update posts in a WordPress database using Yoast SEO, you can run an SQL query to set the Yoast focus keywords to the post’s title if the focus keyword is not already set.

In WordPress, Yoast SEO stores metadata, including focus keywords, in the wp_postmeta table. Each post’s metadata is identified by the post_id that links to the wp_posts table. The meta key for Yoast focus keywords is _yoast_wpseo_focuskw.

Here’s an SQL query to achieve this:

-- Update Yoast focus keywords with the title of the post if not already set
INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
SELECT p.ID, '_yoast_wpseo_focuskw', p.post_title
FROM wp_posts p
LEFT JOIN wp_postmeta pm
  ON p.ID = pm.post_id AND pm.meta_key = '_yoast_wpseo_focuskw'
WHERE p.post_type = 'post' 
  AND p.post_status = 'publish'
  AND pm.post_id IS NULL;

Explanation:

1. wp_posts p: This selects posts from the wp_posts table.
2. LEFT JOIN wp_postmeta pm: Joins the wp_postmeta table where the meta_key is _yoast_wpseo_focuskw (the Yoast focus keyword) to check if the focus keyword exists for the post.
3. WHERE pm.post_id IS NULL: This ensures the query only selects posts that do not have a Yoast focus keyword set.
4. p.post_title: Inserts the post title as the focus keyword.

Additional Notes:

• Make sure to back up your database before running this query.
• Modify wp_ if your database uses a different table prefix.

After running this query, posts without focus keywords will have their titles set as the focus keyword in Yoast SEO [I’m not sure how the re-scoring is triggered, but I went to posts > filter by > and selected ‘posts without keywords’ – I then manually edited a couple and clicked save, and the rest were updated automatically].