SQL Server books online states
Use caution when specifying the
FROMclause to provide the criteria for the update operation. The results of an
UPDATEstatement are undefined if the statement includes a
FROMclause 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
UPDATEstatement is not deterministic.
I need to try and identify cases where non deterministic updates are occurring in our database. It appears that SQL Server does not throw an error or warning in such a case.
Does anyone know if there is an easy way of identifying where these issues are occurring, querying plan cache etc?. Doing a full code review to identify these issues will be time consuming given the size and complexity of the application.
Tested on SQL Server 2008\2017 developer editions.
Here is an example:
declare @t1 table (id int, col int); insert into @t1 values(1,1); declare @t2 table (id int, col int); insert into @t2 values(1,10), (1,20); update t1 set t1.col = t2.col from @t1 t1 join @t2 t2 on t1.id = t2.id; select * from @t1;