更新和连接多行，使用哪一行的值? [英] Updating and join on multiple rows, which row's value is used?

问题描述

假设我有以下语句，内部连接产生 3 行，其中 a.Id = b.Id，但 3 行中的每一行都有不同的 b.Value.由于 tableA 中只有一行被更新，所以更新中使用了 3 个值中的哪一个?

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?

UPDATE a
SET a.Value = b.Value
FROM tableA AS a
INNER JOIN tableB as b 
ON a.Id = b.Id

推荐答案

我认为这种情况没有规则，你不能依赖于特定的结果.

I don't think there are rules for this case and you cannot depend on a particular outcome.

如果您在特定行之后，请说最新的行，您可以使用 apply，例如:

If you're after a specific row, say the latest one, you can use apply, like:

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

这篇关于更新和连接多行，使用哪一行的值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！