SQL Server按插入时间更新列时提示p的ID列被多次指定如何解决

错误原因 #

你编写的查询中子查询同时返回了Tag表的id字段和PageView表的id字段，两个字段未设置别名做区分，外层调用子查询的id字段时SQL无法识别具体指代哪个表的字段，因此抛出列重复错误。另外子查询中不需要添加ORDER BY语句，作为表表达式使用时该语句不会生效。

最优修改方案 #

直接使用关联更新语法即可，不需要嵌套多层子查询：

UPDATE t
SET 
    t.PageViewID = p.id,
    t.BrowserInfoID = p.BrowserInfoID
FROM [Tracking].[Tag] AS t
INNER JOIN [tracking].[PageView] AS p 
    ON p.VisitID = t.VisitID
    AND ABS(DATEDIFF(SECOND, p.CreatedDate, t.CreatedDate)) < 1

一对多匹配场景优化 #

如果存在一条Tag记录匹配多条PageView记录的情况，推荐使用窗口函数取时间差最小的匹配项，避免更新结果不确定：

WITH MatchedPageView AS (
    SELECT 
        t.id AS TagID,
        p.id AS PageViewID,
        p.BrowserInfoID,
        ROW_NUMBER() OVER (PARTITION BY t.id ORDER BY ABS(DATEDIFF(SECOND, p.CreatedDate, t.CreatedDate)) ASC) AS rn
    FROM [Tracking].[Tag] AS t
    INNER JOIN [tracking].[PageView] AS p 
        ON p.VisitID = t.VisitID
        AND ABS(DATEDIFF(SECOND, p.CreatedDate, t.CreatedDate)) < 1
)
UPDATE t
SET 
    t.PageViewID = mpv.PageViewID,
    t.BrowserInfoID = mpv.BrowserInfoID
FROM [Tracking].[Tag] AS t
INNER JOIN MatchedPageView AS mpv ON t.id = mpv.TagID
WHERE mpv.rn = 1

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