Let's say I have table
假设我有桌子
A
一个
Col1 Col2
1 AB
2 CD
B
乙
Col1 Col2
1 EF
1 GH
2 IJ
2 KL
If I have following query
如果我有以下查询
UPDATE A
SET Col2 = B.Col2
FROM A
INNER JOIN B ON A.Col1 = B.Col1
I tried this and the result is
我试过这个，结果是
A
一个
Col1 Col2
1 EF
2 IJ
So, it always update from the first row? Just wanted to make sure the correct behavior because what I'm about to update is quite critical.
那么，它总是从第一行更新？只是想确保正确的行为，因为我即将更新的内容非常关键。
1 个解决方案
#1
1
There is no such a definition as "First row" or "Last row" when you talk about tables in RDBMS. If you have not specify any order the database engine is free to use any records as First record. There are of course exceptions such as existence of clustered indices, collations etc.
在谈论RDBMS中的表时，没有“第一行”或“最后一行”这样的定义。如果您尚未指定任何订单，则数据库引擎可以将任何记录作为第一个记录使用。当然有例外，例如聚集索引的存在，整理等。
If you do not specify any orders you cannot simply assume what the first record would be.
如果您没有指定任何订单，则不能简单地假设第一条记录是什么。
I suggest you modify your query to use a predefined order of the values you want in your update. E.g. if you want to update to a value coming first in alphanumeric order then use this query:
我建议您修改查询以使用更新中所需值的预定义顺序。例如。如果要更新为以字母数字顺序排在第一位的值，请使用以下查询：
UPDATE A
SET Col2 = B.Col2
FROM A
INNER JOIN
(
SELECT Col1, Col2,
ROW_NUMBER() OVER(PARTITION BY Col1 ORDER BY Col2) rn
)
B ON A.Col1 = B.Col1 AND b.rn=1
Please note, that when you specifying an order the order can be set by a completely different column. Imagine you have a DateUpdated column and you wish to use the latest value based on the latest DateUpdated event:
请注意，当您指定订单时，订单可以由完全不同的列设置。想象一下，您有一个DateUpdated列，并且您希望根据最新的DateUpdated事件使用最新值：
UPDATE A
SET Col2 = B.Col2
FROM A
INNER JOIN
(
SELECT Col1, Col2, DateUpdated,
ROW_NUMBER() OVER(PARTITION BY Col1 ORDER BY DateUpdated DESC) rn
)
B ON A.Col1 = B.Col1 AND b.rn=1
#1
1
There is no such a definition as "First row" or "Last row" when you talk about tables in RDBMS. If you have not specify any order the database engine is free to use any records as First record. There are of course exceptions such as existence of clustered indices, collations etc.
在谈论RDBMS中的表时，没有“第一行”或“最后一行”这样的定义。如果您尚未指定任何订单，则数据库引擎可以将任何记录作为第一个记录使用。当然有例外，例如聚集索引的存在，整理等。
If you do not specify any orders you cannot simply assume what the first record would be.
如果您没有指定任何订单，则不能简单地假设第一条记录是什么。
I suggest you modify your query to use a predefined order of the values you want in your update. E.g. if you want to update to a value coming first in alphanumeric order then use this query:
我建议您修改查询以使用更新中所需值的预定义顺序。例如。如果要更新为以字母数字顺序排在第一位的值，请使用以下查询：
UPDATE A
SET Col2 = B.Col2
FROM A
INNER JOIN
(
SELECT Col1, Col2,
ROW_NUMBER() OVER(PARTITION BY Col1 ORDER BY Col2) rn
)
B ON A.Col1 = B.Col1 AND b.rn=1
Please note, that when you specifying an order the order can be set by a completely different column. Imagine you have a DateUpdated column and you wish to use the latest value based on the latest DateUpdated event:
请注意，当您指定订单时，订单可以由完全不同的列设置。想象一下，您有一个DateUpdated列，并且您希望根据最新的DateUpdated事件使用最新值：
UPDATE A
SET Col2 = B.Col2
FROM A
INNER JOIN
(
SELECT Col1, Col2, DateUpdated,
ROW_NUMBER() OVER(PARTITION BY Col1 ORDER BY DateUpdated DESC) rn
)
B ON A.Col1 = B.Col1 AND b.rn=1