sql server 如何在有多个选项的更新语句中选择值? [英] How does sql server choose values in an update statement where there are multiple options?

问题描述

我在 SQL Server 中有一个更新语句，其中可以根据连接分配四个可能的值.SQL 似乎有一种算法可以选择一个值而不是另一个值，我不确定该算法是如何工作的.

I have an update statement in SQL server where there are four possible values that can be assigned based on the join. It appears that SQL has an algorithm for choosing one value over another, and I'm not sure how that algorithm works.

举个例子，假设有一个名为 Source 的表，它有两列(Match 和 Data)结构如下:(匹配列仅包含 1，数据列每行递增 1)
匹配数据
`----------------------------------------
1 1
1 2
1 3
1 4

As an example, say there is a table called Source with two columns (Match and Data) structured as below: (The match column contains only 1's, the Data column increments by 1 for every row)
Match Data
`--------------------------
1 1
1 2
1 3
1 4

该表将更新另一个名为 Destination 的表，该表具有相同的两列结构，如下所示:
匹配数据
`----------------------------------------
1 空

That table will update another table called Destination with the same two columns structured as below:
Match Data
`--------------------------
1 NULL

如果您想通过以下方式更新 Destination 中的 ID 字段:

If you want to update the ID field in Destination in the following way:

更新
目的地
设置
数据 = Source.Data发件人
目的地
内连接
来源
开
Destination.Match = Source.Match

UPDATE
Destination
SET
Data = Source.Data FROM
Destination
INNER JOIN
Source
ON
Destination.Match = Source.Match

在运行此查询后，Destination.ID 将设置为四个可能的选项.我发现弄乱 Source 的索引会对 Destination 的设置产生影响，而且 SQL Server 似乎只是用它找到的第一个匹配的值更新 Destination 表.

there will be four possible options that Destination.ID will be set to after this query is run. I've found that messing with the indexes of Source will have an impact on what Destination is set to, and it appears that SQL Server just updates the Destination table with the first value it finds that matches.

准确吗?SQL Server 是否有可能按顺序使用每个可能的值更新 Destination，而我最终得到的结果与使用它找到的第一个值进行更新的结果相同?它似乎可能会随机选择一行进行更新，而不是在出现这种情况时抛出错误，这似乎可能有问题.

Is that accurate? Is it possible that SQL Server is updating the Destination with every possible value sequentially and I end up with the same kind of result as if it were updating with the first value it finds? It seems to be possibly problematic that it will seemingly randomly choose one row to update, as opposed to throwing an error when presented with this situation.

谢谢.

附言我为糟糕的格式道歉.希望意图是明确的.

P.S. I apologize for the poor formatting. Hopefully, the intent is clear.

推荐答案

它将所有的结果设置为数据.查询后最终得到哪一个取决于返回结果的顺序(最后设置哪一个).

It sets all of the results to the Data. Which one you end up with after the query depends on the order of the results returned (which one it sets last).

由于没有 ORDER BY 子句，您可以使用 Sql Server 提出的任何顺序.这通常遵循磁盘上记录的物理顺序，并且通常遵循表的聚集索引.但是这个顺序并不是一成不变的，尤其是在涉及连接时.如果连接匹配具有非聚集索引的索引的列，则它很可能会根据该索引对结果进行排序.最后，除非你给它一个 ORDER BY 子句，否则 Sql Server 会按照它认为最快的顺序返回结果.

Since there's no ORDER BY clause, you're left with whatever order Sql Server comes up with. That will normally follow the physical order of the records on disk, and that in turn typically follows the clustered index for a table. But this order isn't set in stone, particularly when joins are involved. If a join matches on a column with an index other than the clustered index, it may well order the results based on that index instead. In the end, unless you give it an ORDER BY clause, Sql Server will return the results in whatever order it thinks it can do fastest.

您可以通过将 upate 查询转换为选择查询来玩这个，这样您就可以看到结果.请注意，对于目标表的每条记录，源表中哪条记录最先出现，哪条记录最后出现.将其与更新查询的结果进行比较.然后再次使用索引并再次检查结果以查看结果.

You can play with this by turning your upate query into a select query, so you can see the results. Notice which record comes first and which record comes last in the source table for each record of the destination table. Compare that with the results of your update query. Then play with your indexes again and check the results once more to see what you get.

当然，这里可能很棘手，因为 UPDATE 语句不允许使用 ORDER BY 子句，因此无论您找到什么，您都应该真正编写连接，使其与目标表 1:1 匹配.您可能会发现 APPLY 运算符对实现此目标很有用，您可以使用它来有效地连接到另一个表并保证连接仅匹配一条记录.

Of course, it can be tricky here because UPDATE statements are not allowed to use an ORDER BY clause, so regardless of what you find, you should really write the join so it matches the destination table 1:1. You may find the APPLY operator useful in achieving this goal, and you can use it to effectively JOIN to another table and guarantee the join only matches one record.

这篇关于sql server 如何在有多个选项的更新语句中选择值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！