SQL多次更新同一行(SQL update same row multiple times)

SQL多次更新同一行(SQL update same row multiple times)

我们可以考虑对同一行进行多次更新的必要性的一个示例：

create table t ( c1 int ); insert into t (c1) values (1); insert into t (c1) values (2); insert into t (c1) values (3); update x1 set x1.c1 = X2.new from t as x1 inner join ( select 1 as c1, 100 as new union sel 1, 101 union sel 1, 102 ) as x2 on x1.c1 = x2.c1

同一行有3个潜在的更新。 连接结果集的行数是同一行的3倍。 这一行只访问过一次吗？ 如何计算最终值？

One example where we could consider the necessity of multiple updates to the same row:

create table t ( c1 int ); insert into t (c1) values (1); insert into t (c1) values (2); insert into t (c1) values (3); update x1 set x1.c1 = X2.new from t as x1 inner join ( select 1 as c1, 100 as new union sel 1, 101 union sel 1, 102 ) as x2 on x1.c1 = x2.c1

There are 3 potential updates to the same row. The join result set has 3 times the same row. Is this row visited only once? How is the final value computed?

最满意答案

从UPDATE 文档 ：

指定FROM子句时要小心，以提供更新操作的条件。 如果语句包含未以这样的方式指定的FROM子句，那么UPDATE语句的结果是未定义的，即只有一个值可用于更新的每个列事件，即UPDATE语句不是确定性的。

在这种情况下，结果是不确定的。 最好将这些SQL语句视为错误。

From the UPDATE documentation:

Use caution when specifying the FROM clause to provide the criteria for the update operation. The results of an UPDATE statement are undefined if the statement includes a FROM clause that is not specified in such a way that only one value is available for each column occurrence that is updated, that is if the UPDATE statement is not deterministic.

The results are non deterministic in this cases. Better to consider those SQL statements as errors.