Hello guys. I want to do a mass replacement in a mySQL database. Let's say the table [TABLE], the word that's going to be replaced [OLD] and the new one [NEW]. Could anyone please tell me the correct command to do that? Thank you!

Try this (http://www.mysql.com/doc/R/E/REPLACE.html).


Lats...

As far as I know REPLACE is non SQL92 compliant and is a mysql extension. It is generally a good idea to stay away from such things.

It sounds like you just want to do an UPDATE anyway, here is an example:UPDATE names SET first_nm = 'Joe' WHERE first_nm = 'Henry'will replace all Henry's with Joe.

DizixCom,
I tried the UPDATE command but it didn't work properly. I think that this command updates the columns with specific text. I don't want all the column record to be replaced but only a specific piece of text. For example, I want 'hello dizixcom' to be 'how are you'. All other text in this table/column shouldn't be changed. Is it difficult?

Franki, from what I understand, you only want to replace a particular part of a string in a column?

for this, you would have to select the column, verify if the string you want to replace is present in the column content.. if present replace only that string... else goto next row..

is that what your trying to do?

kunal

Hi

Originally posted by Franki
DizixCom,
I tried the UPDATE command but it didn't work properly. I think that this command updates the columns with specific text. I don't want all the column record to be replaced but only a specific piece of text. For example, I want 'hello dizixcom' to be 'how are you'. All other text in this table/column shouldn't be changed. Is it difficult?

You may use internal function SUBSTRING_INDEX(str,delim,count) theoretically. But it's easy to use php

Regards

Hi Franki,

I think you'll have to take a two step approach to this. There may be some non-standard way to do it in MySQL, but I prefer to stay away from those, who knows you may want to use Oracle some day instead.

Step 1: SELECT field FROM table WHERE field LIKE '%text%'

Step 2: UPDATE table SET field = 'new value' WHERE ...

If using cursors you can use WHERE CURRENT OF and provide the first select statement cursor. If not you'll have to get the primary key as well as the field you are trying to update then do the update WHERE key = ?.

I hope this isn't too confusing. Without knowing what language you are targetting or the capabilities therein it's kind of tough to give a solid example.

Originally posted by DizixCom

Step 1: SELECT field FROM table WHERE field LIKE '%text%'


I wouldnt recommend using this. Since % is a wildcard. And if you wanted to replace just the word "in" in the column with IN, you would replace a lot more.

Example :

Old Column data : today was finally my lucky day. I wasnt able to hit a home run ever since I kicked the pitcher.

New Column data : today was fINally my lucky day. I wasnt able to hit a home run ever sINce I kicked the pitcher.


Not nice, is it? :)

Hmm, he wants to replace a portion of a string therefore he must find the portion he's looking for, wildcards are the only way.

If he knows the complete exact string, then obviously "=" will be more exact, but that doesn't seem to be the case.