Change row values based in another row values (text)

Question

I have a mysql table with usernames and their mail address. I want to update the values in usernames row to be the same as the mail address except the domain. For example, in [email protected] the user will be updated with "jondoe".

Is there any possibility to do this with a query?

Gordon Linoff · Accepted Answer · 2016-10-29 22:49:29Z

1

Does this do what you want?

update t
    set username = substring_index(email, '@', 1);

I should note that different people can have the same email base name on different domains. So, you should be careful that you don't generate duplicates.

If that is a problem, then ask another question.

edited Oct 29, 2016 at 22:49

answered Oct 29, 2016 at 14:09

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

manuel Over a year ago

This query gave me a syntax error. I changed all the names for the ones corresponding to my tables and fields.

LSerni Over a year ago

@manuel, Gordon Linoff will not achieve clairvoyance until he reaches 500k points. What if you told us what that error actually was? That would make it easier to help you. Was the error possibly ERROR 1062 (23000): Duplicate entry...? :-) ).

Gordon Linoff Over a year ago

@isemi . . . The error was stupid. I left out the third argument to substring_index(), and it is now fixed.

Collectives™ on Stack Overflow

Change row values based in another row values (text)

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related