SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Sep 2001
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mysql query question

    I'm sure this is easy, but I am having some problems with it. We have about 75 records in a database @ the same e-mail address that I want to update to a different @ e-mail address. How can I update the domain part of the e-mail and leave the prefix unchanged for these 75 records, for example:

    Here are the currents records with "@eataol.com":

    bob@eataol.com
    judy@eataol.com
    harriet@eataol.com
    etc.

    How do I update them so they are all just "@aol.com"

    bob@aol.com
    judy@aol.com
    harriet@aol.com
    etc.

    Does this make sense? The table name is "login" and the field is "email". I can do this one at a time, but thought that would be well, stupid. Thanks!

  2. #2
    morphine for a wooden leg randem's Avatar
    Join Date
    Jun 2002
    Location
    .chicago.il.us
    Posts
    957
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think this would work...
    Code:
    UPDATE [table_name]
    SET email=REPLACE(email, '@eataol.com', '@aol.com')
    WHERE email LIKE '%@eataol.com'
    ----Adopt-a-Sig----
    Your message here!

  3. #3
    SitePoint Enthusiast
    Join Date
    Sep 2001
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the suggestion, but I tried that but got this:

    Code:
    SQL-query :  
    
    UPDATE [login]SETemail = REPLACE (
    
    email,
    '@eataol.com',
    '@aol.com'
    )
    WHERE email
    LIKE '%@eataol.com' 
    
    MySQL said: 
    
    
    You have an error in your SQL syntax near '[login] SET email=REPLACE(email, '@eataol.com', '@aol.com') WHERE email LIKE '%@' at line 1

    Any other suggestions? Thanks, randem.

  4. #4
    morphine for a wooden leg randem's Avatar
    Join Date
    Jun 2002
    Location
    .chicago.il.us
    Posts
    957
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    how about without brackets?
    Code:
    UPDATE login
    SET email = REPLACE(email, '@eataol.com', '@aol.com')
    WHERE email LIKE '%@eataol.com'
    ----Adopt-a-Sig----
    Your message here!

  5. #5
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what a nifty function. I never knew about that one!

    Aaron
    Aaron Brazell
    Technosailor



  6. #6
    SitePoint Enthusiast
    Join Date
    Sep 2001
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I did try doing it like this, the error message just inserts the brackets (if I'm understanding you correctly):

    Code:
    UPDATE login
    SET email = REPLACE(email, '@eataol.com', '@aol.com')
    WHERE email LIKE '%@eataol.com'
    I got it to work using this:

    Code:
    UPDATE login SET email = CONCAT(LEFT(email,INST (email,"@") -1),"@aol.com") WHERE account = "55555"

    Thanks for the suggestions. I forgot to tell you that these e-mails are associated with account numbers so that simplified at least one side of it. Thanks again!
    Last edited by jamesb; Aug 29, 2002 at 08:22.


Bookmarks

Posting Permissions

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