Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    Regular Coder
    Join Date
    Jul 2008
    Posts
    150
    Thanks
    24
    Thanked 0 Times in 0 Posts

    mysql query to update word in phrase

    hello,
    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 ??

    thanks

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Replace

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,111
    Thanks
    75
    Thanked 4,335 Times in 4,301 Posts
    Guelph's oh so succinct comment means:
    Code:
    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.

  • #4
    Regular Coder
    Join Date
    Jul 2008
    Posts
    150
    Thanks
    24
    Thanked 0 Times in 0 Posts
    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 !!!

  • #5
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

  • #6
    Regular Coder
    Join Date
    Jul 2008
    Posts
    150
    Thanks
    24
    Thanked 0 Times in 0 Posts
    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"
    ,...........

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,111
    Thanks
    75
    Thanked 4,335 Times in 4,301 Posts
    Are you *SURE* you used back-ticks around link????

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

  • Users who have thanked Old Pedant for this post:

    crazy.works (02-19-2009)

  • #8
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

  • #9
    Regular Coder
    Join Date
    Jul 2008
    Posts
    150
    Thanks
    24
    Thanked 0 Times in 0 Posts
    thanks alot it works


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •