如何关联tblContractor与tblProject完成数据归一化并填充ContractorId？

我现在有两张表，需要完成数据归一化，把tblProject里的ContractorId列填充为tblContractor中对应的承包商ID，具体情况如下：

现有表数据 #

tblContractor表（首行为空，有效匹配数据为以下行） #

tblProject表（刚新增ContractorId列，当前值均为NULL） #

需求目标 #

将tblProject的ContractorId更新为tblContractor中对应ContractorName的ContractorId，最终tblProject应如下：

我尝试了下面的SQL语句，但没有达到预期效果：

SELECT project.ProjectId, project.Name, project.ContractorName, project.ContractorId 
FROM tblProject project 
WHERE NOT EXISTS ( 
    SELECT TOP 1 * 
    FROM tblContractor contractor 
    WHERE project.ContractorName = contractor.ContractorName) 
Order by ContractorName

解决方案 #

你尝试的是查询没有匹配承包商的项目，但我们需要的是更新匹配项目的ContractorId，所以应该使用UPDATE结合关联查询的语句，具体如下：

方法1：使用UPDATE JOIN（适用于SQL Server、MySQL等多数主流数据库） #

这是最直接高效的方式，通过关联两张表的ContractorName字段来更新：

UPDATE tblProject p
JOIN tblContractor c ON p.ContractorName = c.ContractorName
SET p.ContractorId = c.ContractorId;

方法2：使用子查询更新（适配不支持JOIN更新的数据库） #

如果你的数据库不支持UPDATE JOIN语法，可以用子查询来实现：

UPDATE tblProject
SET ContractorId = (
    SELECT c.ContractorId
    FROM tblContractor c
    WHERE c.ContractorName = tblProject.ContractorName
)
WHERE EXISTS (
    SELECT 1
    FROM tblContractor c
    WHERE c.ContractorName = tblProject.ContractorName
);

验证结果 #

执行完更新后，你可以用下面的查询验证结果是否符合预期：

SELECT ProjectId, ContractorName, ContractorId
FROM tblProject
ORDER BY ProjectId;

注意事项 #

• 确保两张表的ContractorName完全匹配（比如空格、大小写、末尾的标点，你目标里的SureFix Co和原始的SureFix Co.有个点差异，需要统一，否则会匹配失败）
• 如果tblContractor里存在重复的ContractorName，需要先清理重复数据，否则更新可能会出现不可预期的结果

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