How to find and replace in MySQL

Sometimes you need to change some text in a MySQL table. For example, imagine you want to correct a spelling mistake or a reference in all your WordPress posts without having to change them one by one.

Here’s how to do it with a raw SQL command. This works only on a single table – repeat this for each table:

update `wp_posts`
set `post_content` = replace(post_content, 'Old Text', 'New Text')

Note the use of standard ticks and back ticks here. First we’ll select the table in question (in this case wp_posts), then we’ll issue the find and replace query by selecting the column in the table (post_content), followed by the replace command.

As soon as MySQL comes back all references will have changed from “Old Text” to “New Text”.

Careful here: there is no undo function! Once executed, all changes are live instantly. Make a backup copy of your database before you do anything!





Jay is the CEO and founder of WP Hosting, a boutique style managed WordPress hosting and support service. He has been working with Plesk since version 9 and is a qualified Parallels Automation Professional. In his spare time he likes to develop iOS apps and WordPress plugins, or draw on tablet devices. He blogs about his coding journey at http://wpguru.co.uk and http://pinkstone.co.uk.