CODEWITHSUNDEEP
sql-serversql-update
Pedro Custódio
Pedro Custódio

Reputation: 854

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

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?

Upvotes: 3

Views: 2807

Answers (1)

Pedro Custódio
Pedro Custódio

Reputation: 854

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.

Upvotes: 4

Related Questions