Timo Haapala Timo Haapala - 1 year ago 83
SQL Question

Update several posts' attachment with SQL

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 Source

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*' 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download