How to Find and Replace Data in MySQL

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:

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

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:
UPDATE wp_posts
SET post_content = REPLACE( post_content,  'http://www.oldurl.com',  'http://www.newurl.com' )
WHERE ID < 10

Leave a Reply