Replace DB content on the fly

This appears to be a little known fact, so it has earned the right to be posted as a how-to on my site.

If you have a database with lots of content and you discover to your dismay that you have made a mistake on some of the text, in many rows, you might find yourself asking “How am I going to fix all those errors?”

For example, you have a table with 5000 rows and one column is a URI. You have entered the URI in this database as http://www.mystartupcompany.com/index.php?id=4001, http://www.mystartupcompany.com/index.php?id=4006, http://www.mystartupcompany.com/index.php?id=4009 and so on.

Now you discover that you can have the URI’s in a search engine friendly format that looks like this: http://www.mystartupcompany.com/product/4001, http://www.mystartupcompany.com/product/4006, http://www.mystartupcompany.com/product/4009 and so on.

Can you imagine having to go through the database one by one and correct all 5000 URI’s?

Relax, there is a simple and fast way to do it.

We will assume for arguments sake, that the table name is products, and the column name to modify is prod_uri.

Simply run this command:
UPDATE products SET prod_uri=REPLACE(prod_uri,‘mystartupcompany.com/index.php?id=’,‘mystartupcompany.com/product/’);

Note that the above code is all on one line.

Within a second or two, all the columns and rows will be updated.

Many thanks to Rob over at MacOSXHints for bringing this to my attention.