best way to copy and replace 1000 words in Database?
 

download .sql and open in dreamweaver then do copy and replace word? tnx

If you do that then you also have to have a way to put that back into the database, I assume.

If you are doing all occurrences of a certain word within a table/field than you can do it with a sql update either at the mysql command line or through something like phpmyadmin.

update tablename set field=replace(field,'string_to_replace','string_to _replace_with')

always check yourself first by doing a select using the same syntax to make sure your results will be what you want them to be. Then do a copy of the table before you do the update, just to be 100% sure.

select field, replace(field,'string_2_replace','string_to_replac e_with') from tablename

.

What do you mean copy exactly? You can probably solve your problem with SQL alone.

this is the best way, but make sure you have a backup if you do that, and triple check everything, one wrong move and your whole "field" could get set to null... :thumbsup

http://davidwalsh.name/mysql-replace

basically what sarettah said

just write a script to cycle through your 1000 words and do the sql (if u mean1000 seperate words to replace)

Ah, yeah. I was interpreting his 1000 words as being 1000 occurrences of the same word, not 1000 different words.

If it is 1000 different words then yeah, you want to do up a quick little script for that.

.

If you need someone to do a quick script to do that for you, icq me 33375924 or email me at woj at wojfun dot com

Whatever price WOJ gives you, I will do it for one cent less.


j/k.. Can't go wrong with WOJ :thumbsup

.

Where I used to work we would run like this

Select from table where id="1";

UPDATE table
SET id="1"
where
id="2"
;

Select from table where id="1";

Rollback;

After the SQL ran, View the output results to verify it ran successfully. Then run it without the rollback line. This was with DB2 though. I don't know about MySQL.

Do this, and follow Woj's precaution tips.

What I do, is to build a list of the words I want to replace, put them in Excel, in column A and downwards as far as I need to go (remember to use import as csv or whatever file format you have your 1000 words in).

And then do the same for column B for the words you want to replace with.

Then build a function, in column C, like:
update tablename set field=replace(A1','B1');

where A1 is the word you want to replace and B1 the word you want to replace with. Double click on the black square in the excel cell and let it auto fill downwards.


And then in column D, build then all together, with the & sign, like this:
=C1&C2&C3&C4
to make it return:
update tablename set field=replace(A1','B1');
update tablename set field=replace(A2','B2');
update tablename set field=replace(A3','B3');
update tablename set field=replace(A4','B4');


Then copy the full
update tablename set field=replace(A1','B1');
update tablename set field=replace(A2','B2');
update tablename set field=replace(A3','B3');
update tablename set field=replace(A4','B4');

and paste it into phpmyadmin in the SQL command line and run it.

Just remember that it is, indeed, very powerful, one mistake from you along the way and changes will be done accordingly, so be very careful and take al the necessary precautions!

that would replace the whole field value and set it to the new value, no tjust replace a word within a string (assuming thats what he wanted)

textpad, f8, go!

lot of hassle like that. easier to script it imo.
quick basic script in php assuming using old mysql_ funcs
(completely untested)

glad you got it worked out, but those queries are all wrong, needs to be:
update tablename set field=replace(field,'string_to_replace','string_to _replace_with')
like sarettah explained earlier...

Just a little followup.

If this is indeed searching and replacing a list of 1000 different words, be careful because it is very easy to screw yourself up.

You need to look through the list of replacements. If you are using a word as a replacement word and somewhere else using that word as the word to be replaced then you need to make sure that you do them in the proper order.

for example:

I want to replace:

word 1 with word 2
word 2 with word 3

If I do them in that order then I will replace word 1 with word 2. On the next replace word 2 with word 3 I will be replacing all the original word 2s and all the word 2s that I just created with my replace word 1 with word 2.

So, to achieve the actual results I want I need to do them backwards. I need to first replace word 2 with word 3 then I can do the replace of word 1 with word 2.

That make sense?

.