0

I found this question, which presents the same situation. Trying to do a set-based update, and the table to be updated matchesmultiple rows in the table being used for update values. A minimal example is below:

CREATE TABLE #tempUpdates (ID INT, TheValue VARCHAR(100))

CREATE TABLE #Target (ID INT, TheValue VARCHAR(100))

INSERT INTO #Target (ID, TheValue)
VALUES (1, 'START')

INSERT INTO #tempUpdates (ID, TheValue)
VALUES (1, 'tUpdate'),
(1, 'SecondUpdate')

UPDATE t
SET t.TheValue = u.TheValue
FROM #Target t
INNER JOIN #tempUpdates u ON u.ID = t.ID

SELECT * FROM #Target

The other question's answers went over ways to get a specific update value. What I'm wondering is in the above example, or other similar situations, how does SQL Server decide which value is the new value? First row returned? Something else?

Jeffrey Van Laethem
  • 2,601
  • 1
  • 20
  • 30
  • An arbitrary matching value is chosen. This is highly discouraged in `update` queries. – Gordon Linoff Jun 11 '19 at 15:29
  • Totally understand it's bad practice. Found some legacy code doing this and got curious. But arbitrary? Like SQL Server flips a coin? I was imagining maybe the first row returned, or last row returned, or max rid or something. – Jeffrey Van Laethem Jun 11 '19 at 15:32

0 Answers0