来自同一表中其他列的MySQL Update列 [英] MySQL Update Column from other column in same table

问题描述

我在USERS表中添加了新列supervisor_id，我需要从同一USERS表中填充该列:

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

我将如何遍历表以设置supervisor_id = id，如下所示:

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

我尝试了以下操作，但是很明显，它仅在用户的supervisor_username是他自己的用户名的地方设置了supervisor_id.

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

推荐答案

您可以使用多表UPDATE语法进行自联接:

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

在 sqlfiddle 上查看.

然后，您应该删除SUPERVISOR_NAME列，因为它违反了 3NF ；相反，如果需要，您可以在检索数据时进行另一个自联接:

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

在 sqlfiddle 上查看.

这篇关于来自同一表中其他列的MySQL Update列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！