update statement: one target row, multiple source rows. What are the rules?

Question

Hello,

I am facing an update situation where there is a number of source rows for one target row. The update works with no errors, I am just wondering how it chooses which source value to take for the target row update. In the example below the result is "red", can it be "blue" under other circumstances?
Thanks in advance!

if object_id('t1') is not null drop table t1;
create table t1 (id integer, color varchar(100));

if object_id('c') is not null drop table c;
create table c (id integer, color varchar(100));

insert into t1 values(1, null);
insert into c values(1,'red'), (1,'blue');

update t1
set color = c.color
from t1 inner join c on t1.id = c.id;

select * from t1;

Answer 1

Hi @vy ，

Welcome to the Microsoft SQL Server Q&A Forum！

Regarding your question, I checked some documents.The conclusion drawn from my own perspective is as follows:

When describing the contents of a table, most people usually display the rows in a specific order. But the table actually represents a collection, and the collection has no order. (Tables with clustered index added are stored in the order of the clustered index columns).

The virtual table(there is no order like a normal table) returned by the inner join operator is as follows：

    id  color   id  color  
    1 NULL 1 red  
    1 NULL 1 blue  

The update statement returns a row from the virtual table to update the t1.color column. The column that satisfies the condition t1.id = c.id has two columns. It is uncertain which column is returned.SQL Server will return the row that happened to be accessed first.Therefore, different results may be produced, but they can all be considered correct. If you want to ensure the certainty of the results, you can choose to include a unique order by list.

Take the select statement as an example:

select top(1) *  
from c   
order by color  

The above select statement specifies a unique order by list (the color field is unique), so the returned result is certain.

The result returned by the following statement is not certain (of course they are all considered correct)：

select top(1) *  
from c   
order by id  
  
select top(1) *  
from c   

In short, SQL Server will return the row that happens to be accessed first, and which row is accessed first is up to the developer.

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @vy ,

Welcome to MS Q&A and thank you for asking this question.

In my understanding, if there is no OrderBy in the SQL Query (like your example here), after Inner-Join (Inner Join compares and finds all the matching rows in the table c), update query will always update the value of table t1 with the first matching row (which inserted first in table c) from table c.

Please let me know if this helps. If my response helps, please "accept as answer" and "up-vote" it! Thank you!

Answer 3

The result will be a random value.

It may be 9,999,999,999 it results in "red" and 1 time the compiler decides to do it a different way and it results "blue" and you will pull your hair out for days to trying to figure out why.

Answer 4

It's not deterministic. Tom said "random", but that's inexact. As long as the query plan is the same, it is very likely that you will get the same value every time. At least if the plan is serial. But if the plan changes - you could also get a different value.

The important thing is that you cannot rely on anything here. Not even the value being randomly chosen.

Answer 5

@Nasreen Akter @Tom Phillips @Erland Sommarskog @EchoLiu-MSFT @Dan Guzman
Thank you so much guys! It is so kind of you to have given your expert answers!!
All of them are to the point, and all of them answer my question.
I wish I could mark all of the answers as "accepted", but the system allows me to make one choice only. I chose the most elaborative respond, but again - all of them are great and deserve a kudos for sure!
PS: I am amazed I got responded by Erlang and Dan, I have a feeling I got answered by Gods :))