mySQL Statement to update field 1

[amanxman]

Hey,

Background: Standard mySQL database, on php with myphpAdmin running.

I have a field "urlPath", which contains a value, say "/example/imagexx.jpg"

I then have another field "urlPath2" which is blank at moment.

This second field needs to have the value based on the first, for example "/example/two/imagexx.jpg"

So, is there a way to write a SQL statement to say:
1) Get value of urlPath
2) Insert /two/ into a specific place of that value
3) Update this new value into urlPath2

Now I have previously run this through a PHP script to update urlPath2 based on it's value in urlPath --- but

a) I have 110,000 records of this, and PHP script doesn't like that and runs out of memoery, so I have to split the php script into segments, so takes a fair time to actually run through all records

b) I want to learn how I should do it through SQL!


Few notes:
# imagexx is actually the value of field 'num' - So i could pull it from there, but figured as urlPath is fully poulated with /example/imagexx.jpg that would be the best to base urlPath2 on

# I have this three times (urlPath2,urlPath3 and urlPath4), but any one script I can obviously rerun for 3 and 4.

Thanks in advance!

 

[r937]

UPDATE daTable
   SET urlPath2 = 
       REPLACE(urlpath,'/example/','example/two/')

this will operate on all rows in the table

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[amanxman]

Excellent, perfect!

Thank you