Limiting Search and Replace to Category

Question

I wish to find and replace a certain keyword across a major site.

I would normally use something like

UPDATE wp_posts 
   SET post_content = REPLACE (post_content, 'Item to replace here', 'Replacement text here');  

However, I don't want the update to be across the entire site, I wish to limit it to only one category.

Can anyone tell me how to achieve this?

Answer 1

UPDATE: To be more precise the query should look

UPDATE wp_term_relationships r JOIN wp_posts p 
    ON r.object_id = p.id JOIN wp_terms t
    ON r.term_taxonomy_id = t.term_id JOIN wp_term_taxonomy x
    ON r.term_taxonomy_id = x.term_taxonomy_id
   SET p.post_content = REPLACE(post_content, 'Item to replace here', 'Replacement text here')
 WHERE t.name = 'My Category'
   AND x.taxonomy = 'category'
   AND x.parent = 0 -- top level category

Note: before you update anything:

1) you can check what posts will be affected by issuing

SELECT *
  FROM wp_term_relationships r JOIN wp_posts p 
    ON r.object_id = p.id JOIN wp_terms t
    ON r.term_taxonomy_id = t.term_id JOIN wp_term_taxonomy x
    ON r.term_taxonomy_id = x.term_taxonomy_id
 WHERE t.name = 'My Category'
   AND x.taxonomy = 'category'
   AND x.parent = 0  -- top level category

2) make sure you have a solid backup of your wordpress database.