UPDATE with a one-to-many JOIN (multiple columns)

Question

I've run into an issue and I'm not sure if it is the intended behaviour. I have searched online for an answer, but all I could find was about what row would update last and so what would be the value after the query. I know there is no such thing as order in that case, and you can't be sure what the value would be ahead of time.

In my case though, I'm updating different columns, so overriding a previous update is not a concern of mine.

CREATE TABLE #original (id int, value1 int, value2 int)
INSERT INTO #original (id) VALUES (1), (2)

CREATE TABLE #temp (id int, name varchar(10), value int)
INSERT INTO #temp (id, name, value) VALUES (1, 'value1', 10), (1, 'value2', 11), (2, 'value1', 20), (2, 'value2', 21)

SELECT * FROM #original

id          value1      value2
----------- ----------- -----------
1           NULL        NULL
2           NULL        NULL

SELECT * FROM #temp

id          name       value
----------- ---------- -----------
1           value1     10
1           value2     11
2           value1     20
2           value2     21

UPDATE O SET 
    value1 = CASE WHEN T.name = 'value1' THEN T.value ELSE value1 END,
    value2 = CASE WHEN T.name = 'value2' THEN T.value ELSE value2 END
FROM
    #original O
        INNER JOIN #temp T ON T.id = O.id

SELECT * FROM #original

id          value1      value2
----------- ----------- -----------
1           10          NULL
2           20          NULL

I don't get why both value2 are NULL.

SELECT
    O.id,
    CASE WHEN T.name = 'value1' THEN T.value ELSE value1 END AS value1,
    CASE WHEN T.name = 'value2' THEN T.value ELSE value2 END AS value2
FROM
    #original O
        INNER JOIN #temp T ON T.id = O.id

id          value1      value2
----------- ----------- -----------
1           10          NULL
1           NULL        11
2           20          NULL
2           NULL        21

Running the above instead of the update, it looks exactly like what I thought it would and I would assume it means "four" updates, populating both value1 and value2 in both rows.

I would really appreciate if someone could explain this one to me.

Answer 1

Modified the update clause with a little tweak in getting the value1 and value2

;with cte as (SELECT
    O.id,
    max(CASE WHEN T.name = 'value1' THEN T.value ELSE value1 END) AS value1,
    max(CASE WHEN T.name = 'value2' THEN T.value ELSE value2 END) AS value2

FROM
    #original O
        INNER JOIN #temp T ON T.id = O.id
        group by o.id
        )
UPDATE o SET 
    o.value1 = i.value1 ,
    o.value2 = i.value2 

FROM
    #original o
        INNER JOIN cte  i ON i.id = O.id