How can i update one value with another value in mysql

Question

How can I update the username field with the email field value for all the records on the table.

update table1 set username = (select email from table1);

I know it is wrong method. But I am not getting the correct way to do this.

Answer 1

Sample Data:

Uname  Email
a      abc@mail.com
b      def@mail.com
c      ghi@mail.com

• If you want your output to be:

  Uname            Email
  abc@mail.com     abc@mail.com
  def@mail.com      def@mail.com
  ghi@mail.com      ghi@mail.com
  

  Then use:

  UPDATE table1 set Uname  = EMAIL
  

• If you want your output to be

  Uname            Email
  abc@mail.com      abc@mail.com
  b                 def@mail.com
  c                 ghi@mail.com
  

  Then use,

  UPDATE TABLE1 SET UNAME = EMAIL WHERE UNAME='a';
  

• If you want your output to be

  Uname            Email
  abc@mail.com      abc@mail.com
  abc@mail.com      def@mail.com
  abc@mail.com      ghi@mail.com
  

  Then use,

  UPDATE TABLE1 SET UNAME = (SELECT EMAIL FROM TABLE1 WHERE UNAME='a');
  

Answer 2

Simply use

UPDATE table1 SET username = email;