SQL 更新和连接多行，使用哪一行的值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9502449/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Updating and join on multiple rows, which row's value is used?
提问by sooprise
Let's say I have the following statement and the inner join results in 3 rows where a.Id = b.Id, but each of the 3 rows have different b.Value's. Since only one row from tableA is being updated, which of the 3 values is used in the update?
假设我有以下语句，内部连接产生 3 行，其中 a.Id = b.Id，但 3 行中的每一行都有不同的 b.Value。由于只有 tableA 中的一行被更新，更新中使用了 3 个值中的哪一个？
UPDATE a
SET a.Value = b.Value
FROM tableA AS a
INNER JOIN tableB as b
ON a.Id = b.Id
采纳答案by Andomar
I don't think there are rules for this case and you cannot depend on a particular outcome.
我认为这种情况没有规则，您不能依赖特定的结果。
If you're after a specific row, say the latest one, you can use apply, like:
如果您在特定行之后，请说最新的行，您可以使用apply，例如：
UPDATE a
SET a.Value = b.Value
FROM tableA AS a
CROSS APPLY
(
select top 1 *
from tableB as b
where b.id = a.id
order by
DateColumn desc
) as b
回答by JamieSee
Usually what you end up with in this scenario is the first row that appears in the order of the physical index on the table. In actual practice, you should treat this as non-deterministic and include something that narrows your result to one row.
通常，在这种情况下，您最终得到的是按照表上物理索引的顺序出现的第一行。在实际实践中，您应该将其视为非确定性的，并包含将结果缩小到一行的内容。
回答by sudhAnsu63
Best option in my case for updating multiple records is to use merge Query(Supported from SQL Server 2008), in this query you have complete control of what you are updating. Also you can use output query to do further processing.
在我的情况下，更新多条记录的最佳选择是使用合并查询（SQL Server 2008 支持），在此查询中，您可以完全控制要更新的内容。您也可以使用输出查询进行进一步处理。
Example: Without Output clause(only update)
示例：无输出子句（仅更新）
;WITH cteB AS
( SELECT Id, Col1, Col2, Col3
FROM B WHERE Id > 10 ---- Select Multiple records
)
MERGE A
USING cteB
ON(A.Id = cteB.Id) -- Update condition
WHEN MATCHED THEN UPDATE
SET
A.Col1 = cteB.Col1, --Note: Update condition i.e; A.Id = cteB.Id cant appear here again.
A.Col2 = cteB.Col2,
A.Col3 = cteB.Col3;
Example: With OputPut clause
示例：使用 OputPut 子句
CREATE TABLE #TempOutPutTable
{
PkId INT NOT NULL,
Col1 VARCHAR(50),
Col2 VARCHAR(50)
}
;WITH cteB AS
( SELECT Id, Col1, Col2, Col3
FROM B WHERE Id > 10
)
MERGE A
USING cteB
ON(A.Id = cteB.Id)
WHEN MATCHED THEN UPDATE
SET
A.Col1 = cteB.Col1,
A.Col2 = cteB.Col2,
A.Col3 = cteB.Col3
OUTPUT
INSERTED.Id, cteB.Col1, A.Col2 INTO #TempOutPutTable;
--Do what ever you want with the data in temporary table
SELECT * FROM #TempOutPutTable; -- you can check here which records are updated.
回答by Justin Pihony
Here is what I came up with using SQL Server 2008
这是我想出的使用 SQL Server 2008 的方法
--drop table #b
--drop table #a
select 1 as id, 2 as value
into #a
select 1 as id, 5 as value
into #b
insert into #b
select 1, 3
insert into #b
select 1, 6
select * from #a
select * from #b
UPDATE #a
SET #a.Value = #b.Value
FROM #a
INNER JOIN #b
ON #a.Id = #b.Id
It appears that it uses the top value of a basic select each time (row 1 of select * from #b). So, it possibly depends on indexing. However, I would not rely on the implementation set by SQL, as that has the possibility of changing. Instead, I would suggest using the solution presented by Andomar to make sure you know what value you are going to choose.
它似乎每次都使用基本选择的最高值（select * from #b 的第 1 行）。因此，它可能取决于索引。但是，我不会依赖 SQL 设置的实现，因为它有可能改变。相反，我建议使用 Andomar 提供的解决方案来确保您知道要选择的值。
In short, do not trust the default implementation, create your own. But, this was an interesting academic question :)
总之，不要相信默认的实现，创建你自己的。但是，这是一个有趣的学术问题:)
回答by E.Gannon
Yes, I came up with a similar experiment to Justin Pihony:
是的，我想出了一个与 Justin Pihony 类似的实验：
IF OBJECT_ID('tempdb..#test') IS NOT NULL DROP TABLE #test ;
SELECT
1 AS Name, 0 AS value
INTO #test
IF OBJECT_ID('tempdb..#compare') IS NOT NULL DROP TABLE #compare ;
SELECT 1 AS name, 1 AS value
INTO #compare
INSERT INTO #compare
SELECT 1 AS name, 0 AS value;
SELECT * FROM #test
SELECT * FROM #compare
UPDATE t
SET t.value = c.value
FROM #test t
INNER JOIN #compare c
ON t.Name = c.name
Takes the topmost row in the comparison, right-side table. You can reverse the #compare.value values to 0 and 1 and you'll get the reverse. I agree with the posters above...its very strange that this operation does not throw an error message as it is completely hiddenthat this operation IGNORES secondary values
获取比较中最上面的一行，右侧表。您可以将 #compare.value 值反转为 0 和 1，您将得到相反的结果。我同意上面的海报......很奇怪这个操作不会抛出错误消息，因为它完全隐藏了这个操作忽略次要值
