MySQL Update Column from other column in same table
mysql
Solution
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.
Problem
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 ```