Occasionally, you may want to update specific posts to have all links attributed with rel=”nofollow”. For example, if you have sponsored posts on your site you may want them to have the rel=”nofollow” attribute so that google does not index those external links on your site. I client of mine asked me to do this the other day, so here is how to do it in WordPress.
The following step assumes you have tagged all your posts with a specific tag_id. If you’ve done that, the rest is relatively easy. As always, whenever you are updating database records in bulk make sure you backup your database first. The following SQL consists of 2 parts. Part 1 gets post IDs that should be updated. And Part 2 does the actual update based on the list of post IDs. The only way to make this query work as a single query is to use cursors to traverse through Post IDs. Since I can’t be bothered with cursors, I simply run this in 2 steps.
select p.ID from wp_posts p inner join wp_term_relationships tr ON (tr.object_id = p.ID) inner join wp_term_taxonomy tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id) inner join wp_terms t ON (tt.term_id = t.term_id) WHERE t.term_id = your_term_id
where your_term_id is the ID of the Tag you are looking up. This will output a list of IDs and what I do now is, I copy the column of IDs and replace line breaks with commas so that I can use that list in my 2nd query.
UPDATE wp_posts SET post_content = REPLACE(post_content, 'href="', 'rel="nofollow" href="') WHERE ID IN(comma_separated_list_of_ids)
Where comma_separated_list_of_ids is a result set from Query 1
Marko