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

Related posts:

  1. Voting Functionality in a website
  2. How to get size of BLOB in MySQL
  3. Sample MySQL table
  4. What does int(11) means in MySQL?

Leave a Reply