MySQL update column from another column in one table

Question

I added a new supervisor_id column to the USERS table, which I need to populate from the same USERS table:

ID | USERNAME | SUPERVISOR_USERNAME | SUPERVISOR_ID 1 | jdoe | jsmith | NULL 2 | jsmith | dduck | NULL 

How would I scroll the table to set supervisor_id = id, for example:

 ID | USERNAME | SUPERVISOR_USERNAME | SUPERVISOR_ID 1 | jdoe | jsmith | 2 2 | jsmith | dduck | NULL 

I tried the following, but it obviously only installs a supervisor, where the supervisor_username user is his own username.

 update users set supervisor_id = id where supervisor_username = username 

Answer 1

You can make self-join with the syntax of several UPDATE tables:

 UPDATE users u JOIN users s ON s.SUPERVISOR_USERNAME = u.USERNAME SET u.SUPERVISOR_ID = s.ID 

Take a look at sqlfiddle .

Then you must omit the SUPERVISOR_NAME column, which violates 3NF ; instead, you can do another self-destruct when retrieving data, if necessary:

 SELECT u.ID, u.USERNAME, s.USERNAME AS SUPERVISOR_USERNAME, u.SUPERVISOR_ID FROM users u LEFT JOIN users s ON s.ID = u.SUPERVISOR_ID 

Take a look at sqlfiddle .

Answer 2

 update Users u inner join Users temp on u.Supervisor_username = temp.UserName set u.Supervisor_ID = temp.ID