update可以作为mysql的列,来自同一表中其他列的MySQL Update列

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

解决方案

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.