Howto Change all instances of a word in a Mysql database

This how to refers to an Smf Forum database but can just as easily be applied to a WordPress, or any other Mysql database.

I recently re-opened my old forum which is 5 years old and has a lot of old posts, people have changed usernames, email addresses. The forum has had three domain names, so it stands to reason that there is a lot to tidy up. So I set about updating a few things today.

The basic mysql syntax (which can be launched from Phpmyadmin Sql Tab) is this:

UPDATE table SET field_name = REPLACE(field_name, ‘tony’, ‘TONY’)

For example, to change an old url link to a new one in the body of forum posts:

UPDATE smf_messages SET body = REPLACE(body, ‘myoldurl.com’, ‘mynewurl.com’)

Obviously, you just change the table, field and text to be replaced.


Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s