How to Find and Replace Data in MySQL

Recently, while migrating my blog, I had to find all the occurrences of my old URL and replace it with my URL. One way of doing this was to get a database dump, open it in a text editor and the do a find replace and the import it back. This is a 4 step process. However, I found that we can do this only in 1 step directly in MySQL using the UPDATE statement with REPLACE function.

Syntax:

1 2	UPDATE [table_name] SET [field] = REPLACE([field], '[string_to_find]', '[string_to_replace]')

Replace the [table_name], [field_name], [string_to_find] and [string_to_replace] with your specific information and then execute it.

Note: Always take a backup of your database before executing this command, in case something goes wrong. Also, it is a good idea to test the query on a local copy before running it on your production database.

Example code to replace the URL in wp_posts table

1 2	UPDATE wp_posts SET post_content = REPLACE(post_content, 'http://www.oldurl.com', 'http://www.newurl.com')

This above code will replace all the occurrences of “http://www.oldurl.com” in post_content field of the wp_posts table with “http://www.newurl.com”. If you want to perform the replace only on specific rows, say with id < 10, then you can use a query similar to this:

1 2 3

UPDATE wp_posts SET post_content = REPLACE(post_content, 'http://www.oldurl.com', 'http://www.newurl.com') WHERE ID < 10