SQL Server：基于Table2批量更新Table1的keyField字段求助

这个问题很常见，本质是你的子查询没有正确关联到Table1的每一行，或者提取keyField中数字后缀的逻辑有疏漏，导致子查询返回了多行结果。结合你的表结构，给你几个可靠的解决方案：

方案1：使用JOIN更新（推荐） #

用JOIN关联两张表是更高效、更安全的方式，完全避免子查询多行的问题，逻辑也更清晰：

UPDATE Table1 t1
JOIN Table2 t2 
  ON t2.Id = CAST(SUBSTRING_INDEX(t1.keyField, '_', -1) AS UNSIGNED)
SET t1.keyField = t2.value;

逻辑说明： #

• SUBSTRING_INDEX(t1.keyField, '_', -1)：从keyField中提取下划线后面的数字部分（比如test_500会得到500），这个函数比固定长度的SUBSTRING更灵活，即使前缀长度变化也能正确拿到后缀数字。
• CAST(xxx AS UNSIGNED)：把提取到的字符串数字转成整数，和Table2的主键Id类型匹配（如果Table2的Id是字符串类型，可以去掉CAST）。
• 通过JOIN直接关联两行数据，确保每一行Table1都能匹配到唯一的Table2记录（因为Table2的Id是主键，唯一）。

方案2：修正子查询写法 #

如果你坚持要用子查询，需要确保子查询只返回单行结果，并且正确关联当前行的Table1数据：

UPDATE Table1 t1
SET keyField = (
    SELECT t2.value
    FROM Table2 t2
    WHERE t2.Id = CAST(SUBSTRING_INDEX(t1.keyField, '_', -1) AS UNSIGNED)
    LIMIT 1 -- 保险起见，强制返回单行（Table2 Id是主键的话其实不需要）
);

为什么之前报错？ #

你原来的子查询大概率没有正确关联Table1的当前行，或者substring(expression)的写法错误，导致子查询返回了Table2的多条记录，而非当前Table1行对应的唯一记录。

验证步骤（重要） #

在执行更新前，建议先运行SELECT语句验证关联逻辑是否正确，避免误更新：

SELECT 
  t1.Id, 
  t1.keyField AS original_key,
  SUBSTRING_INDEX(t1.keyField, '_', -1) AS extracted_id,
  t2.value AS target_value
FROM Table1 t1
JOIN Table2 t2 
  ON t2.Id = CAST(SUBSTRING_INDEX(t1.keyField, '_', -1) AS UNSIGNED);

这个语句会显示所有即将被更新的行和对应的目标值，确认没问题后再执行UPDATE。

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