mysql - 无法从左连接中选择正确的信息

Question

我正在将字符串字段更改为引用。我已经隐藏了旧数据并更改了列类型，所以现在我只是尝试使用旧列中的正确信息来更新新转换的列。

我有以下逻辑:

UPDATE myapp_assignment SET home_hr_support = 
  (SELECT id FROM 
      (SELECT * FROM
      (SELECT home_hr_support_old, home_hr_support FROM myapp_assignment) a
    LEFT JOIN
      (SELECT id, CONCAT(first_name, ' ', last_name) AS fullname FROM users) b
    ON a.home_hr_support_old = b.fullname
    WHERE fullname = home_hr_support_old LIMIT 1
  ) c 
); 

SELECT * FROM
    (SELECT home_hr_support_old, home_hr_support FROM myapp_assignment) a
  LEFT JOIN
    (SELECT id, CONCAT(first_name, ' ', last_name) AS fullname FROM users) b
  ON a.home_hr_support_old = b.fullname;

这会返回如下所示的结果行:

home_hr_support_old | home_hr_support | id | fullname
----------------------------------------------------------
Clark Kent          | 1               | 1  | Clark Kent
Clark Kent          | 1               | 1  | Clark Kent
Homer Simpson       | 1               | 3  | Homer Simpson
Homer Simpson       | 1               | 3  | Homer Simpson
Jon Snow            | 1               | 5  | Jon Snow
Jon Snow            | 1               | 5  | Jon Snow

如您所见，我正在运行的查询未使用正确的用户编号更新 home_hr_support 列。

我希望 Homer Simpson 的 home_hr_support 值为 3，而 Jon Snow 的值为 5。

我觉得我已经接近找到解决方案了，但我显然做错了什么。有人愿意插话并指出我的错误吗？

====

编辑:DDL 和相同的 fiddle :

CREATE TABLE `myapp_assignment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `home_hr_support` int(11) DEFAULT NULL,
  `home_hr_support_old` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_13D6985C1F4E43E0` (`home_hr_support`)
) ENGINE=InnoDB AUTO_INCREMENT=501 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(180) COLLATE utf8_unicode_ci NOT NULL,
  `first_name` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `last_name` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO myapp_assignment (id, home_hr_support, home_hr_support_old) VALUES(1, NULL, "Clark Kent");
INSERT INTO myapp_assignment (id, home_hr_support, home_hr_support_old) VALUES(2, NULL, "Clark Kent");
INSERT INTO myapp_assignment (id, home_hr_support, home_hr_support_old) VALUES(3, NULL, "Homer Simpson");
INSERT INTO myapp_assignment (id, home_hr_support, home_hr_support_old) VALUES(4, NULL, "Homer Simpson");
INSERT INTO myapp_assignment (id, home_hr_support, home_hr_support_old) VALUES(5, NULL, "Jon Snow");
INSERT INTO myapp_assignment (id, home_hr_support, home_hr_support_old) VALUES(6, NULL, "Jon Snow");

INSERT INTO users (id, username, first_name, last_name) VALUES(1, "ckent", "Clark", "Kent");
INSERT INTO users (id, username, first_name, last_name) VALUES(3, "hsimpson", "Homer", "Simpson");
INSERT INTO users (id, username, first_name, last_name) VALUES(5, "jsnow", "Jon", "Snow");

https://www.db-fiddle.com/f/2faiyqhVFWqnfNPi91eF96/0

Answer 1

考虑以下...

SELECT * 
  FROM myapp_assignment x 
  JOIN users y 
    ON CONCAT_WS(' ',y.first_name,y.last_name) = x.home_hr_support_old;
+----+-----------------+---------------------+----+----------+------------+-----------+
| id | home_hr_support | home_hr_support_old | id | username | first_name | last_name |
+----+-----------------+---------------------+----+----------+------------+-----------+
|  1 |            NULL | Clark Kent          |  1 | ckent    | Clark      | Kent      |
|  2 |            NULL | Clark Kent          |  1 | ckent    | Clark      | Kent      |
|  3 |            NULL | Homer Simpson       |  3 | hsimpson | Homer      | Simpson   |
|  4 |            NULL | Homer Simpson       |  3 | hsimpson | Homer      | Simpson   |
|  5 |            NULL | Jon Snow            |  5 | jsnow    | Jon        | Snow      |
|  6 |            NULL | Jon Snow            |  5 | jsnow    | Jon        | Snow      |
+----+-----------------+---------------------+----+----------+------------+-----------+

UPDATE myapp_assignment x 
  JOIN users y 
    ON CONCAT_WS(' ',y.first_name,y.last_name) = x.home_hr_support_old 
   SET x.home_hr_support = y.id;

SELECT * FROM myapp_assignment;
+----+-----------------+---------------------+
| id | home_hr_support | home_hr_support_old |
+----+-----------------+---------------------+
|  1 |               1 | Clark Kent          |
|  2 |               1 | Clark Kent          |
|  3 |               3 | Homer Simpson       |
|  4 |               3 | Homer Simpson       |
|  5 |               5 | Jon Snow            |
|  6 |               5 | Jon Snow            |
+----+-----------------+---------------------+