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.