Replace embedded post links after changing domains

And now for a short SQL lesson in a case study.

Ever do this? Map a domain to a sub-blog, post away merrily for a while (or a user does) and then one day decide you like another domain name better? Yeah. I hate when that happens. Usually because I get to fix it.

No problem! We just re-map the new domain and we’re good to go. But wait! Now all our images embedded in posts have gone *poof*. I mean, they’re still there on the server, but now we have link in posts with absolute URLs pointing to the old domain. Bummer.

Open up phpMyAdmin or whatever you use to manage your database. Click on the SQL tab because we’re gonna run a query. The query below, in fact.

update `wp_BLOGID#_posts`
set `post_content` = replace(`post_content`,’olddomain.com’,'newdomain.com’)

The above basically says in the posts table of this particular blog, change all instance of olddomain.com to newdomain.com. make sure you get the right blog ID # of the blog in question.