基于文本键匹配表：SELECT正常但UPDATE语句执行异常求助

需求说明 #

我需要通过clients.client（VARCHAR类型）和clients_info.client（VARCHAR类型）这两个文本键匹配两张表：

• clients_info与clients为多对一关系（clients_info中存在多条相同拼写的记录，对应clients中唯一的匹配记录）
• clients_info表无ID字段，希望用clients.idx的值填充clients_info的对应字段（或更新clients表的关联字段，根据语句推测可能存在需求混淆）

验证匹配关系的SELECT语句 #

我已通过以下SELECT语句确认了正确的匹配关联，能将clients_info的多条记录对应到clients的目标idx：

SELECT 
  clients_info.client, 
  clients.client AS c_client, 
  clients.idx 
FROM clients
INNER JOIN clients_info ON (clients_info.client = clients.client);

执行结果：

client           | c_client        |  idx
------------------------------------------
Real Corp        | Real Corp        | 180
Real Corp        | Real Corp        | 180
Real Corp        | Real Corp        | 180
Advantage People | Advantage People |  29
Advantage People | Advantage People |  29
Advantage People | Advantage People |  29
Others           | Others           |  392
Others           | Others           |  392

错误的UPDATE尝试 #

我尝试了以下两个UPDATE语句，但均未得到正确结果：

1. 拼写错误+逻辑偏差：

UPDATE clients 
INNER JOIN clients_info ON (clients.client = clients_info.client)
SET clients.client_info_id = cllients.idx; -- 存在拼写错误：cllients → clients，且若目标是填充clients_info，此处更新表选错

2. 子查询逻辑错误：

UPDATE clients SET clients.client_info_id = (
    SELECT idx
    FROM clients_info
    WHERE clients_info.client = clients.client
    ); -- clients_info表无idx字段，子查询无法返回有效数据

正确的UPDATE语句 #

情况1：给clients_info填充对应clients.idx #

如果需求是给clients_info添加并填充关联的clients.idx（需先新增对应字段）：

-- 先新增字段（若未添加）
ALTER TABLE clients_info ADD COLUMN client_idx INT;

-- 执行更新
UPDATE clients_info
INNER JOIN clients ON clients_info.client = clients.client
SET clients_info.client_idx = clients.idx;

情况2：更新clients表的client_info_id字段 #

若确实需要更新clients表的关联字段，因是多对一关系，需确保子查询返回唯一值：

UPDATE clients
SET client_info_id = (
    SELECT clients.idx
    FROM clients_info
    WHERE clients_info.client = clients.client
    LIMIT 1 -- 取第一条匹配记录，或用聚合函数确保结果唯一
);

也可使用JOIN方式更新：

UPDATE clients
INNER JOIN (
    SELECT DISTINCT client, idx FROM clients
) AS c ON clients.client = c.client
SET clients.client_info_id = c.idx;

内容的提问来源于stack exchange，提问作者JREAM