[FIXED] Update Meta Value for WordPress posts with SQL

Issue

I'm trying to update the meta value for all of my posts in a WordPress database that match a certain criteria.

I have a table I created called update_with_false where each row "value" matches the post_name of a post in the wp_posts table.

What I need to do is change the meta_value for the meta_key "included_in_related_articles" to "true" for each post in the wp_posts table that has the same post name as the rows in the update_with_false table.

Here is my SQL right now, but I'm getting a syntax error:

UPDATE SET wp_postmeta (post_id, meta_key, meta_value)
select ID, 'included_in_related_articles', 'true' from wp_posts
WHERE update_with_false.value = wp_posts.post_name

Hopefully that makes sense. I've attached a couple of screenshots of the meta fields I need to update and of the extra table I created

Thanks!

Solution

If I understand correctly, you want to update wp_postmeta.meta_value and set it to true if the value in wp_posts.post_name is found in update_with_false.value.

You can do this by first joining wp_posts.post_name column to the wp_postmeta table and then limiting the joined posts by further joining update_with_false table.

I'm writing this off the top of my head (with a little help from this ER diagram):

update wp_postmeta pm
  inner join wp_posts p on p.id = pm.post_id
    inner join update_with_false x on x.value = p.post_name
set pm.meta_value = 'true'
where pm.meta_key = 'included_in_related_articles'

Answered By - Bostjan