使用一对多 JOIN 进行更新（多列）

我遇到了一个问题，我不确定这是否是预期的行为。我在网上搜索了答案，但我所能找到的只是关于最后更新的行以及查询后的值是多少。我知道在这种情况下不存在订单之类的东西，并且您无法提前确定其价值是多少。

但就我而言，我正在更新不同的列，因此覆盖以前的更新不是我关心的问题。

CREATE TABLE #original (id int, value1 int, value2 int)
INSERT INTO #original (id) VALUES (1), (2)

CREATE TABLE #temp (id int, name varchar(10), value int)
INSERT INTO #temp (id, name, value) VALUES (1, 'value1', 10), (1, 'value2', 11), (2, 'value1', 20), (2, 'value2', 21)

SELECT * FROM #original

id          value1      value2
----------- ----------- -----------
1           NULL        NULL
2           NULL        NULL

SELECT * FROM #temp

id          name       value
----------- ---------- -----------
1           value1     10
1           value2     11
2           value1     20
2           value2     21

UPDATE O SET 
    value1 = CASE WHEN T.name = 'value1' THEN T.value ELSE value1 END,
    value2 = CASE WHEN T.name = 'value2' THEN T.value ELSE value2 END
FROM
    #original O
        INNER JOIN #temp T ON T.id = O.id

SELECT * FROM #original

id          value1      value2
----------- ----------- -----------
1           10          NULL
2           20          NULL

我不明白为什么两者value2 are NULL.

SELECT
    O.id,
    CASE WHEN T.name = 'value1' THEN T.value ELSE value1 END AS value1,
    CASE WHEN T.name = 'value2' THEN T.value ELSE value2 END AS value2
FROM
    #original O
        INNER JOIN #temp T ON T.id = O.id

id          value1      value2
----------- ----------- -----------
1           10          NULL
1           NULL        11
2           20          NULL
2           NULL        21

运行上面的内容而不是更新，它看起来与我想象的完全一样，我认为这意味着“四个”更新，填充两者value1 and value2在两行中。

如果有人能向我解释这一点，我将非常感激。

这里的问题是你的假设：“但就我而言，我正在更新不同的列，因此覆盖以前的更新不是我关心的问题。” 最肯定的是is一个问题，因为最终，你的代码

value1 = CASE WHEN T.name = 'value1' THEN T.value ELSE value1 END,
value2 = CASE WHEN T.name = 'value2' THEN T.value ELSE value2 END

每行仅运行（或生效）一次。 SQL Server 不需要将同一行更新两次，并且通常会任意采用单行的值，其中之一是NULL.

文档指出 https://learn.microsoft.com/en-us/sql/t-sql/queries/update-transact-sql?view=sql-server-ver15#best-practices （我的大胆）:

指定时要小心FROM子句提供更新操作的条件。结果UPDATE声明是不明确的如果该语句包含FROM未以这样一种方式指定的子句：对于更新的每个列出现只有一个值可用，即如果UPDATE声明不是确定性的。例如，在UPDATE在以下脚本中的语句中，表 1 中的两行均满足FROM中的条款UPDATE陈述;但未定义使用 Table1 中的哪一行来更新 Table2 中的行。

Only one row可能会被使用并完成一次更新，但您不能假设更新将按顺序发生。所以你需要确保你有一个single匹配您要更新的每一行。

因此你应该预先汇总你的价值观

UPDATE O SET 
    value1 = T.value1,
    value2 = T.value2
FROM
    #original O
INNER JOIN (
    SELECT
      id,
      MAX(CASE WHEN T.name = 'value1' THEN T.value END) value1,
      MAX(CASE WHEN T.name = 'value2' THEN T.value END) value2,
    FROM #temp T
    GROUP BY
      id
) T ON T.id = O.id;

你也可以使用CROSS APPLY或 CTE。