如何基于表值函数的输出更新SQL数据表的指定列

报错原因 #

你遇到的报错主要是因为原UPDATE语句的子查询在遇到State表中Course字段为空、或者对应课程在Course表中无匹配记录时，会返回空结果集，无法完成赋值操作。

正确实现方案 #

SQL Server 提供的APPLY运算符就是专门用于逐行将左表的字段作为参数传入右表的表值函数，关联返回结果的场景，完全匹配你的需求：

方案1：仅更新存在匹配课程的记录 #

使用CROSS APPLY，仅会更新State表中Course字段能在Course表中找到对应记录的行：

UPDATE s
SET s.MajorVersion = f.MajorVersion
FROM State s
CROSS APPLY fn_get_latest_major_course(s.Course) f

方案2：更新所有记录，无匹配时版本号设为NULL #

使用OUTER APPLY，会更新State表的所有行，找不到匹配课程的MajorVersion会被设为NULL：

UPDATE s
SET s.MajorVersion = f.MajorVersion
FROM State s
OUTER APPLY fn_get_latest_major_course(s.Course) f

多列同步更新 #

如果需要同时用函数返回的多个字段更新State表的多列，直接在SET后追加对应字段即可，示例：

UPDATE s
SET s.MajorVersion = f.MajorVersion,
    s.LatestCourseId = f.CourseID
FROM State s
OUTER APPLY fn_get_latest_major_course(s.Course) f

性能优化建议 #

针对数万条记录的更新场景，可以给Course表建联合覆盖索引，大幅提升查询速度：

CREATE NONCLUSTERED INDEX IX_Course_CatalogNumber_CreatedDate 
ON dbo.Course (CatalogNumber, CreatedDate DESC)
INCLUDE (MajorVersion, CourseID)

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