How does sql server choose values in an update statement where there are multiple options?

Solution 1:

It sets all of the results to the Data. Which one you end up with after the query depends on the order of the results returned (which one it sets last).

Since there's no ORDER BY clause, you're left with whatever order Sql Server comes up with. That will normally follow the physical order of the records on disk, and that in turn typically follows the clustered index for a table. But this order isn't set in stone, particularly when joins are involved. If a join matches on a column with an index other than the clustered index, it may well order the results based on that index instead. In the end, unless you give it an ORDER BY clause, Sql Server will return the results in whatever order it thinks it can do fastest.

You can play with this by turning your upate query into a select query, so you can see the results. Notice which record comes first and which record comes last in the source table for each record of the destination table. Compare that with the results of your update query. Then play with your indexes again and check the results once more to see what you get.

Of course, it can be tricky here because UPDATE statements are not allowed to use an ORDER BY clause, so regardless of what you find, you should really write the join so it matches the destination table 1:1. You may find the APPLY operator useful in achieving this goal, and you can use it to effectively JOIN to another table and guarantee the join only matches one record.

Solution 2:

The choice is not deterministic and it can be any of the source rows.

You can try

DECLARE @Source TABLE(Match INT, Data INT);

INSERT INTO @Source
VALUES
(1, 1),
(1, 2),
(1, 3),
(1, 4);

DECLARE @Destination TABLE(Match INT, Data INT);

INSERT INTO @Destination
VALUES
(1, NULL);


UPDATE Destination
SET    Data = Source.Data
FROM   @Destination Destination
       INNER JOIN @Source Source
               ON Destination.Match = Source.Match; 

SELECT *
FROM @Destination;

And look at the actual execution plan. I see the following.

The output columns from @Destination are Bmk1000, Match. Bmk1000 is an internal row identifier (used here due to lack of clustered index in this example) and would be different for each row emitted from @Destination (if there was more than one).

The single row is then joined onto the four matching rows in @Source and the resultant four rows are passed into a stream aggregate.

The stream aggregate groups by Bmk1000 and collapses the multiple matching rows down to one. The operation performed by this aggregate is ANY(@Source.[Data]).

The ANY aggregate is an internal aggregate function not available in TSQL itself. No guarantees are made about which of the four source rows will be chosen.

Finally the single row per group feeds into the UPDATE operator to update the row with whatever value the ANY aggregate returned.

If you want deterministic results then you can use an aggregate function yourself...

WITH GroupedSource AS
(
SELECT Match,
       MAX(Data) AS Data
FROM @Source
GROUP BY Match
)
UPDATE Destination
SET    Data = Source.Data
FROM   @Destination Destination
       INNER JOIN GroupedSource Source
               ON Destination.Match = Source.Match; 

Or use ROW_NUMBER...

WITH RankedSource AS
(
SELECT Match,
      Data,
      ROW_NUMBER() OVER (PARTITION BY Match ORDER BY Data DESC) AS RN
FROM @Source
)
UPDATE Destination
SET    Data = Source.Data
FROM   @Destination Destination
       INNER JOIN RankedSource Source
               ON Destination.Match = Source.Match
WHERE RN = 1; 

The latter form is generally more useful as in the event you need to set multiple columns this will ensure that all values used are from the same source row. In order to be deterministic the combination of partition by and order by columns should be unique.