It is sometimes necessary to do a bulk update of WordPress post content, for example if you’ve moved the site to a new domain and need to change domain references for links and images.
NB: Make sure to backup your database before using any of these techniques! It’s very easy to completely bork your site with these tools.
For site-wide domain reference updates (for example when moving from a staging server to production), it’s best to use a tool that is safe for serialised data. Interconnect IT’s Database Search and Replace script is great for this purpose. There are also plugins such as Better Search and Replace that can perform similar functions from the WordPress admin. However, if you break something using a plugin, you will probably break WordPress and won’t be able to do another search and replace to fix the problem. The Interconnect IT script sits outside of WordPress and can thus recover from this situation.
You can also export the database as SQL from phpMyAdmin, open the file in a text editor, perform the search and replace there, and re-import using phpMyAdmin. However, this won’t work with serialised data (often used in the wp_options table) and it can be problematic re-importing a large database because of time-outs.
However, sometimes you just want to a simple text search and replace in the posts table. Perhaps you’ve changed your company name. Or you want to change an attribute on a shortcode that you use on every page. And you might want to only change the latest published post, not the old revisions. In these situations, a simple SQL query can be the answer.
UPDATE `wp_posts` SET `post_content` = REPLACE(post_content, 'text to replace', 'replacement text') WHERE post_type='posts' AND post_status='publish'
