View Full Version : mysql query to update word in phrase

02-17-2009, 01:04 PM
i have alot of data in table that i cant update each element alone and i want to change one word in the all elements with one query

if i have table called "data" , column "column_name", in the "column_name" for example
phrase = "http://sitename.com/path/1111.jpg" ,
phrase = "http://sitename.com/path/2222.jpg" ,
phrase = "http://sitename.com/path/333.jpg" , .......................

i wanna make the all links http://mysite.com not http://sitename.com and the rest of the paths will stay as it is

so can i update one word in the all data in that column ??


02-17-2009, 03:48 PM

Old Pedant
02-18-2009, 01:44 AM
Guelph's oh so succinct comment means:

UPDATE `data` SET `column_name` = Replace( `column_name`, 'http://sitename.com', 'http://mysite.com' )

But caution: Unlike most MySQL functions, it appears that REPLACE is case sensitive when it comes to that second argument. So you might need to do a couple of REPLACE calls if you have (example) 'http://sitename.com' and 'HTTP://sitename.com' and 'http://SiteName.com', etc.

02-18-2009, 04:06 AM
thanks , but i tried UPDATE `data` SET `column_name` = Replace( `column_name`, 'http://sitename.com', 'http://mysite.com' );

and i got the output the column_name !!! .
it updated the all data in the column and changed it to the column name it self !!!

02-18-2009, 03:19 PM
can you show us the query that you actually ran? the query you have posted there will not change the data into the column name.

02-19-2009, 05:15 AM
okay i have the table called users and the column name called link
link = "http://sitename.com/path/1111.jpg" , where userid = "1"
link = "http://sitename.com/path/2222.jpg" , where userid = "2"
link = "http://sitename.com/path/333.jpg" , where userid = "3"
,........... alot of data

so i executed that query
UPDATE `users` SET `link` = Replace( `link`, 'http://sitename.com', 'http://mysite.com' );

and i thought it gonna be like that
link = "http://mysite.com/path/1111.jpg" , where userid = "1"
link = "http://mysite.com/path/2222.jpg" , where userid = "2"
link = "http://mysite.com/path/333.jpg" , where userid = "3"

but it became like that
link = "link" , where userid = "1"
link = "link" , where userid = "2"
link = "link" , where userid = "3"

Old Pedant
02-19-2009, 07:02 AM
Are you *SURE* you used back-ticks around link????

UPDATE `users` SET `link` = Replace( `link`, 'http://sitename.com', 'http://mysite.com' );

Because what you are seeing REALLY looks like you use apostrophes, instead. Like this:

UPDATE `users` SET `link` = Replace( 'link', 'http://sitename.com', 'http://mysite.com' );

Why not just kill the ticks/apostrophes completely??? So far as I know, link isn't a MySQL keyword, so it doesn't need them. Just try:

UPDATE `users` SET link = Replace( link, 'http://sitename.com', 'http://mysite.com' );

Yeah, I just checked the INDEX in the MySQL docs, and "LINK" is *NOT* listed as a keyword, so that should work fine.

02-19-2009, 02:55 PM
I just tested it, the only way to make the change you are suggesting happened is that you used single quotes around your column name.

02-19-2009, 03:33 PM
thanks alot it works