making mass edits in SQL database

[d-null]

anyone have any pointers for me in doing some database editing?

I would like to change one field in all of the entries in my database... is there a search and replace utility around, or can you recommend a method?

[Bro Media - BANNED FOR LIFE]

update yourtable set field_name = 'newvalue'

[d-null]

thanks for the reply, but I didn't explain my question very well

what I want to do is in a big sql database, I would like to mass edit replace many records all at once

say for example I wanted to change every occurence of the word "Blue Elephant" to read instead "Yellow Elephant", but only in one category

how can I do a search and replace in my entire mysql with one easy move?

[polle54]

Originally Posted by jetjet

thanks for the reply, but I didn't explain my question very well

what I want to do is in a big sql database, I would like to mass edit replace many records all at once

say for example I wanted to change every occurence of the word "Blue Elephant" to read instead "Yellow Elephant", but only in one category

how can I do a search and replace in my entire mysql with one easy move?

Okay let's say that the text "Blue Elephant" is in a field called animal_name.

You tables is named animals.

go into phpmyadmin or something like that

write:

update animals set animal_name = "Yellow Elephant" where animal_name LIKE "Blue Elephant"

Now this is if you can replace the whole name.

if you need to change something in the middle of a text it will look something like this:

tablename stil animals.
Fieldname now animal_text

the content og the animal_text could be something like "this is once again a blue elephant walking around"

your statement should look something like this:

update animals set animal_text = REPLACE(animal_text, "Blue", "Yellow") where animal_text LIKE '%Blue%'

This statement should replace all occurrences of the word Blue. adding the where clause with the LIKE statement is actually just for performance, can be left out if performance is not an issue here.

It's out of the top of my head so it might not be working 100% but then read up on using replace() in a sql update.

[polle54]

short answer I think would be:

UPDATE yourtablename SET columnname = REPLACE(columnname, 'Text to Replace', 'Text to Replace with')

[d-null]

thanks polle, that will get me started

[darksoul]

Originally Posted by polle54

update animals set animal_text = REPLACE(animal_text, "Blue", "Yellow") where animal_text LIKE '%Blue%'

The sky is Yellow.