best way to copy and replace 1000 words in Database? - GoFuckYourself.com

lakerslive · 11-27-2012, 02:53 PM

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

sarettah · 11-27-2012, 03:28 PM

Quote:

Originally Posted by lakerslive

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

.

sixsax · 11-27-2012, 03:29 PM

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

woj · 11-27-2012, 03:37 PM

Quote:

Originally Posted by sarettah

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

.

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...

scouser · 11-27-2012, 04:10 PM

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)

sarettah · 11-27-2012, 04:26 PM

Quote:

Originally Posted by deadmoon

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.

.

woj · 11-27-2012, 04:35 PM

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

sarettah · 11-27-2012, 04:56 PM

Quote:

Originally Posted by woj

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

.

lezinterracial · 11-27-2012, 05:21 PM

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.

Adraco · 11-27-2012, 05:27 PM

Quote:

Originally Posted by sarettah

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

.

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!

scouser · 11-27-2012, 05:34 PM

Quote:

Originally Posted by lezinterracial

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.

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)

lucas131 · 11-27-2012, 05:37 PM

textpad, f8, go!

scouser · 11-27-2012, 05:38 PM

Quote:

Originally Posted by Adraco

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!

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

PHP Code:


			
<?
$words = explode("\n","word1,replaceme
word2,replaceme2,
word3,replaceme 3
etc...");
foreach($words as $word) {
$word = explode(",",$word);
mysql_query('update   table set field = replace(field,{$word[0]},{$word[1]}) ');
}

?>

(completely untested)

woj · 11-27-2012, 05:46 PM

Quote:

Originally Posted by Adraco

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!

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...

sarettah · 11-27-2012, 07:30 PM

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?

.

11-27-2012, 02:53 PM	#1
lakerslive Confirmed User Industry Role: Join Date: Aug 2012 Posts: 929	best way to copy and replace 1000 words in Database? download .sql and open in dreamweaver then do copy and replace word? tnx

11-27-2012, 04:35 PM	#7
woj <&(©¿©)&> Industry Role: Join Date: Jul 2002 Location: Chicago Posts: 47,882	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 __________________ Custom Software Development, email: woj#at#wojfun#.#com to discuss details or skype: wojl2000 or gchat: wojfun or telegram: wojl2000 Affiliate program tools: Hosted Galleries Manager Banner Manager Video Manager Wordpress Affiliate Plugin Pic/Movie of the Day Fansign Generator Zip Manager

11-27-2012, 05:21 PM	#9
lezinterracial Confirmed User Industry Role: Join Date: Jul 2012 Posts: 3,068	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. __________________ Live Sex Shows

11-27-2012, 07:30 PM	#15
sarettah see you later, I'm gone Industry Role: Join Date: Oct 2002 Posts: 14,075	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? . __________________ All cookies cleared! Last edited by sarettah; 11-27-2012 at 07:31 PM..

11-27-2012, 03:29 PM	#3
sixsax Confirmed User Industry Role: Join Date: Aug 2006 Location: Denmark Posts: 213	What do you mean copy exactly? You can probably solve your problem with SQL alone.

11-27-2012, 04:10 PM	#5
scouser marketer. Industry Role: Join Date: Aug 2006 Location: bcn Posts: 2,280	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)

11-27-2012, 05:37 PM	#12
lucas131 ¯\_(ツ)_/¯ Industry Role: Join Date: Aug 2004 Posts: 11,475	textpad, f8, go!