Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

mySQL Statement to update field 1

Status
Not open for further replies.

amanxman

Technical User
Oct 8, 2005
19
NZ
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!
 
Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top