来自同一表中其他列的 MySQL 更新列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17684628/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Update Column from other column in same table
提问by Katie M
I added a new column, supervisor_id, to a USERS table that I need to populate from the same USERS table:
我向需要从同一个 USERS 表填充的 USERS 表中添加了一个新列 supervisor_id：
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:
我将如何遍历表来设置 supervisor_id = id，如下所示：
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.
我尝试了以下操作，但它显然只设置了 supervisor_id，其中用户的 supervisor_username 是他自己的用户名。
update users
set supervisor_id = id
where supervisor_username = username
回答by eggyal
You can make a self-join with the multiple table UPDATEsyntax:
您可以使用多表UPDATE语法进行自联接：
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_NAMEcolumn, which violates 3NF; instead, you can make another self-join when you retrieve the data if so desired:
然后SUPERVISOR_NAME，您应该删除违反3NF 的列；相反，如果需要，您可以在检索数据时进行另一个自联接：
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.
回答by Pakk
update Users u
inner join Users temp on
u.Supervisor_username = temp.UserName
set u.Supervisor_ID = temp.ID
