1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Mass Modify Database Values

Discussion in 'Databases' started by HRA, Nov 28, 2015.

  1. #1
    Hi,
    SEMrush
    Please kindly take a look at this screenshot:
    [​IMG]

    It shows some records in my website database
    Im looking for an easy way to do the following

    For eg:
    I want to change all records under "user_login" to relevant record under "user_email" from ID 124 to ID 134 (or any no)

    So if I take few examples from the screenshot:

    Change "user_login" vihanga_vsm to
    Change "user_login" Umar to
    etc
    also do this for from specific ID to specific ID
    For eg: from ID 02 to 5400

    Is there an easy way to do this? Any SQL query I can run?

    Thanks
     
    Solved! View solution.
    HRA, Nov 28, 2015 IP
    SEMrush
  2. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,630
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #2
    Why don't you just update the login-code to use the user_email instead of user_login? Much easier, I would assume.
    If that's not an option, just do something like this:
    
     UPDATE `tablename` SET `user_login` = `user_email`
    
    Code (markup):
    and if you want to limit it for just some IDs, do something like this:
    
     UPDATE `tablename` SET `user_login` = `user_email` WHERE `ID` <= 02 AND `ID` => 5200
    
    Code (markup):
     
    PoPSiCLe, Nov 28, 2015 IP
  3. HRA

    HRA Active Member

    Messages:
    315
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    60
    #3
    Thanks a lot for the reply.

    The reason I want to do this is, when I started the site, users required to use Username + password to login. Lately I noticed its hard for users to remember usernames. So I changed it to E-mail + Password. After Sept. 02, when a user registers, his user_login automatically set to his email. But for previously registered accounts, Username stayed as user_login.
    There wasn't any problems until WP update 4.3.1
    After that, whenever user registered prior to changes enter an email + pass to login, it doesn't let him login, because his user_login is a Username, not an Email.
    I manually changed some Usernames in user_login to Emails and it worked. But there are nearly 5000 members.
    That's why I asked for an easy way.

    There is one more thing.
    About IDs, I noticed sometimes, some IDs were skipped.
    For eg:
    there are users registered with
    IDs 9, 10, 11
    Then like 12, 14, 15
    You see 13 is skipped.

    So if I use the second query you mentioned, this won't be a problem right?

    Thanks
     
    HRA, Nov 28, 2015 IP
  4. HRA

    HRA Active Member

    Messages:
    315
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    60
    #4
    I ran this query to change values of user IDs 02 to 90
    UPDATE `wp_users` SET `user_login` = `user_email` WHERE `ID` <= 02 AND `ID` => 90

    But I get this error:
    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '=> 90' at line 1

    What should I do?
    Thanks
     
    HRA, Nov 29, 2015 IP
  5. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,630
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #5
    Might be it's just the syntax - try using >= instead. If that doesn't work, just change it to > 91.
     
    PoPSiCLe, Nov 29, 2015 IP
  6. HRA

    HRA Active Member

    Messages:
    315
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    60
    #6
    Sorry for late reply. I didn't get the new reply email notification.

    I tried both of these:
    UPDATE `wp_users` SET `user_login` = `user_email` WHERE `ID` <= 02 AND `ID` >= 90
    UPDATE `wp_users` SET `user_login` = `user_email` WHERE `ID` <= 02 AND `ID` > 91

    I get this:
    0 row(s) affected. ( Query took 0.0005 sec )

    Any ideas?

    Thanks
     
    HRA, Dec 2, 2015 IP
  7. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,630
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #7
    So... it doesn't affect any rows, but it does run the query - are you sure the IDs between 02 and 91 are in the database?

    It should work... maybe I'm overlooking something basic... But right now I can't really see what that might be.
     
    PoPSiCLe, Dec 2, 2015 IP
  8. HRA

    HRA Active Member

    Messages:
    315
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    60
    #8
    Just to make sure, I chose 96 to 98.
    Still I get that msg but no changes.

    Thanks
     
    HRA, Dec 3, 2015 IP
  9. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,630
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #9
    Okay, could you post a couple of lines of the database with the relevant ids?
    Just, row 02 to 06 or something.
     
    PoPSiCLe, Dec 3, 2015 IP
  10. HRA

    HRA Active Member

    Messages:
    315
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    60
    #10
    You can checkout the screenshot in the first post mate.

    Also I posted this question on SitePoint too and one member suggested that I could use this:
    update `wp_users`
    set `user_login` = `user_email`

    I did and apparently it did what I wanted. Im still double checking to make sure it didn't break anything.

    Thanks
     
    HRA, Dec 4, 2015 IP
  11. #11
    Duh... I reread what I wrote, and it was a stupid error on my part:
    This:
    
    UPDATE `wp_users` SET `user_login` = `user_email` WHERE `ID` <= 02 AND `ID` >= 90
    
    Code (markup):
    should've been this:
    
    UPDATE `wp_users` SET `user_login` = `user_email` WHERE `ID` >= 02 AND `ID` <= 90
    
    Code (markup):
    The larger than/less than signs were pointing the wrong way - of course you wouldn't get any hits on an ID less than two and larger than 90... *head hits desk*
     
    PoPSiCLe, Dec 5, 2015 IP
  12. HRA

    HRA Active Member

    Messages:
    315
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    60
    #12
    Just saw your reply. It works now.
    Thanks
     
    HRA, Dec 24, 2015 IP