Use regular expressions to edit an entire column with MySQL
I have a table like so:
| link | ---------- http://dev.site.com/images/image1.png http://dev.site.com/images/image2.png http://dev.site.com/images/image3.png http://dev.site.com/images/image4.png
But, I need to change it so it contains:
| link | ---------- http://site.com/resources/images/image1.png http://site.com/resources/images/image2.png http://site.com/resources/images/image3.png http://site.com/resources/images/image4.png
There are lots of entries that need changing. What would be a good way to go about updating all of the entries?
Right now I'm using this to find the entries:
SELECT column FROM table WHERE link REGEXP '^dev'
I was updating the links by hand first, but there are so many left. I can't update them all myself.
You call it "Field" in the table listing but then "link" in the SQL so i'm going to use "Field"
update table set Field = replace(Field, 'dev.site.com', 'site.com/resources') where Field rlike '^dev.site.com'