MySQL Update Column from other column in same table [duplicate]

Question

I added a new column, supervisor_id, to a USERS table that 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 loop through the table to set the supervisor_id = id, like this:

ID   |  USERNAME  |  SUPERVISOR_USERNAME  |  SUPERVISOR_ID

1    |  jdoe      |  jsmith               |  2

2    |  jsmith    |  dduck                | NULL

I tried the following, but it obviously only set the supervisor_id where the user's supervisor_username was his own username.

update users
set supervisor_id = id
where supervisor_username = username

Answer 1

You can make a self-join with the multiple table UPDATE syntax:

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

See it on sqlfiddle.

You should then drop your SUPERVISOR_NAME column, which violates 3NF; instead, you can make another self-join when you retrieve the data if so desired:

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

See it on sqlfiddle.

Answer 2

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