Mass Modify Database Values

Hi,

Please kindly take a look at this screenshot:


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

 

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):

 

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

 

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

 

Might be it's just the syntax - try using >= instead. If that doesn't work, just change it to > 91.

 

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

 

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.

 

Just to make sure, I chose 96 to 98.
Still I get that msg but no changes.

Thanks

 

Okay, could you post a couple of lines of the database with the relevant ids?
Just, row 02 to 06 or something.

 

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

 

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*

 

Just saw your reply. It works now.
Thanks