SQL 多次更新同一行 [英] SQL update same row multiple times

问题描述

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

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

同一行有 3 个潜在的更新.连接结果集有 3 次相同的行.这一行是否只访问过一次?最终值是如何计算的?

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 the UPDATE documentation:

在指定 FROM 子句以提供更新操作的条件时要小心.如果 UPDATE 语句包含的 FROM 子句未指定为每个更新的列出现只有一个值，即 UPDATE 语句不是确定性的，则 UPDATE 语句的结果是未定义的.

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.

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

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

这篇关于SQL 多次更新同一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！