UDATE Statement with JOIN to Table with Duplicate Rows

No points...

AFAIK, that type of update is always non-deterministic and whatever order the optimizer chooses isn't something you should bank on. Changing the sql so it's deterministic statement is the only way to ensure a consistent result.

Did you see this thread?
https://www.experts-exchange.com/questions/28704278/UPDATE-Based-on-Join-nondeterministic-example.html 

That addresses my first part of the question in which I knew a many-to-one join was non-deterministic. My main question is the second part, where a many-to-many joinis used. I would think, as I stated, that if table1 had 4 rows with a value of 1 for col1 and table2 had 4 rows with a value of 1 for col1, although which row is joined on is non-deterministic, once one row is chosen as the candidate, will that row's col2 value be used to update all rows in table1 that have a value of 1 for col1?

The answer is simple:

It is non-deterministic.  The optimizer may chose under certain circumstances the same execution plan every time, this behavior is not guaranteed.

@DB - Just to echo what Ste5an already said, I think the point is with non-deterministic behavior you can't make that assumption. The optimizer might produce that result, but there's no guarantee of that - which is what you're asking. If you need a predictable and consistent result, you must use a deterministic statement.

Well, I'm not 100% convinced I got a definitive answer. The issue is not repeatability, as I'm not looking for that.  
The question was, given the following data:

  table1          table2
col1  col2      col1  col2
  1     2         1     5
  1     3         1     6 
  1     4         2     8
  2     1         3     0         
  3     6         4     7 

Select allOpen in new window

Using this query:

UPDATE t1
SET    col2 = t2.col2
FROM   table1 t1
INNER JOIN table2 t2
ON     t1.col1 = t2.col1

Select allOpen in new window

I understand that which value is chosen from table2 to update table1 (for col1 value = 1) and that it isn't repeatable (e.g. if it updates with 5 on one run, it might update with 6 the next run). My primary question is, if it selects to update table1.col2 with 6 for the first row, will it use 6 for the 2nd and 3rd rows, or is is possible to have it update row 1 and 3 with 5 and row 2 with 6?

Further comment: I know I can make this deterministic using a window function. I am asking because this is existing production code and changing it is a big deal and can be done if necessary but so far it has 'behaved.' I would prefer a "yes" or "no" answer and not an "AFAIK" or "I believe" or "it should."

I wrote a query to load the tables, run the update, write the results of table1 to a temp table, then reset all the data in table1. I put it in a loop and ran it 100K times and it was consistent, but like I said, I could run it 10M times and it might be different once.

If the first run updates the columns with 5 and another run updates with 6, that is not what I'm concerned about. If it updates with 5, will all rows that have a col1 value of 1 be updated with the same value for col2?

Based on the link that _agx_ posted, it states, "SQL Server silently performs a nondeterministic UPDATE where one of the source rows arbitrarily “wins.” Does the one row that "wins" win for all rows that get updated with the matching JOIN?

While @Scott is right, that the query plan is compiled and then used, this does not mean that the same value is used.

A simple sample (CIX update) shows that already with a few rows:

USE [ExpertsExchange]
GO

DROP TABLE IF EXISTS T1;
DROP TABLE IF EXISTS T2;
GO

CREATE TABLE T1 ( C1 INT NOT NULL, C2 INT NOT NULL ) ;
CREATE CLUSTERED INDEX CIX_T1 ON T1 ( C2 ASC );

CREATE TABLE T2 ( C1 INT NOT NULL, C2 INT NOT NULL ) ;
CREATE CLUSTERED INDEX CIX_T2 ON T2 ( C1 ASC );
GO

INSERT INTO T1 
VALUES	( 1, 1 ),
		( 2, 1 ),
		( 3, 1 ),
		( 4, 1 ),
		( 1, 2 ),
		( 2, 2 ),
		( 3, 2 ),
		( 4, 2 ),
		( 1, 3 ),
		( 2, 3 ),
		( 3, 3 ),
		( 4, 3 ),
		( 1, 4 ),
		( 2, 4 ),
		( 3, 4 ),
		( 4, 4 );

INSERT INTO T2
VALUES	( 1, 10 ),
		( 1, 20 ),
		( 1, 30 ),
		( 1, 40 ),
		( 2, 2 ),
		( 3, 3 ),
		( 4, 4 );
GO

UPDATE T1
SET    T1.C2 = T2.C2
FROM   T1
	INNER JOIN T2 ON T1.C1 = T2.C1;
GO

SELECT	*
FROM	T1;
GO

Select allOpen in new window

Thus for your questions: The query plan, once compiled, can be called deterministic, but NO, the same value does NOT have to be used by it.

Test instance at hands: Microsoft SQL Server 2019 (RTM-GDR) (KB4583458) - 15.0.2080.9 (X64)   Nov  6 2020 16:50:01   Copyright (C) 2019 Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 19042: ) (Hypervisor)

I said that you should see either a stream aggregate operation or a spool operation in the plan, and, IF you do, then the plan will be consistent.  

If you create clus indexes on the values, you give SQL the false impression that the values are unique, and then you may have issues because you will get a plan w/o one of those correction operations.

For example, in your code above, drop the word CLUSTERED out of the index creation.  Then the query plan contains a Stream Aggregate operation.

It's just a sample, showing that you don't see either a stream aggregate or spool operation. Besides that, the plan is always "consistent" or "deterministic" and these terms make no sense here.

The question was, whether the update uses the same value or not. Nothing more. The physical structure of the data, aka clustered indices is not defined and may change.

Thus, due to the scope of the question without making assumptions: Yes, the value can be the same or not. The query alone does not determine this.

EDIT.

I said that you should see either a stream aggregate operation or a spool operation in the plan, and, IF you do, then the plan will be consistent.

I don't see the necessity that parallel streams use the same spool value in the case e.g. of a large partitioned table.

Stan and Scott, thank you for the clarifications. In this case, there are clustered indexes in place on col1 on both tables (the JOINed columns). I will play around with it and see what I can determine. Believe it or not, our DBAs have the systems (even DEV) locked down so tight that we don't have SHOWPLAN access or access to any DMVs or other tools that would make our jobs easier. If I want to see a query plan I have to submit the query to our DBAs and ask them to run the script. :-(

Several people had already stated that it was not a consistent UPDATE in SQL Server.

Therefore, I was trying to point out the exceptions as to when it would be consistent.

Of course the best thing would be to use some method to ensure consistency.

Btw, your DBAs are total tools for not allowing you to see a query plan.  They must have Oracle DBAs in the past, who tend to super-lock everything because of how fragile Oracle plans are.

You'd certainly want to add OPTION (MAXDOP 1) to the query:

UPDATE a SET col2 = b.col2 FROM table1 a INNER JOIN table2 b ON b.col1 = a.col1 OPTION (MAXDOP 1);

Thanks all. Like I said, coming up with a solution to fix it isn't the issue. Getting the change made and pushed to production is. What I was trying to determine is, is it worth the effort to try to get it updated or just leave as-is. I think at this point, I'm going to leave as-is and hopefully we will have some other change in the near future and I can push in a fix then.