Update several posts' attachment with SQL

Question

I've tried in several different ways to write an SQL query that would insert/update post's image attachment, but I haven't succeeded. I'd like to change several posts' image to be the same.

In other words I'd like to update postmeta to value XXX where post's title contains certain string. The problem is that posts' titles are in posts table and not in the postmeta table so I would have to somehow get the title from posts using post's id.

This is what I've tried:

SELECT ID FROM wp_posts WHERE post_title LIKE ‘%*part of title*%’;

UPDATE wp_postmeta
SET meta_value = *attatchment-post’s id*
WHERE post_id IN (*post* *id’s which attachment is to be updated*) AND meta_key = ‘*my attachment field*’;

The second part works if I manually list all of the posts' id's. Basically id like to use the result of the SELECT query in the second UPDATE query.

Any ideas, thanks?

Answer 1

Doing it in the same line as you have written -

UPDATE wp_postmeta
SET meta_value = (select meta_value from wp_postmeta where post_id = *attatchment-post’s id* AND meta_key = '*my attachment*')
WHERE post_id IN (SELECT ID FROM wp_posts WHERE post_title LIKE ‘%*part of title*%’;
) AND meta_key = '*my attachment*' 

Answer 2

In MySQL you can use joins in the update statements as well, and lift the where criteria from the select statement over to combine it with the update's where criteria:

update wp_postmeta wpm
inner join wp_posts wp on wpm.post_id=wp.id
set wpm.meta_value=*attatchment-post’s id*
where  wpm.meta_key = ‘*my attachment field*’ and wp.post_title LIKE ‘%*part of title*%’