Updating multiple columns using case conditional clause

Question

In the following TSQL code the second column TypeId2 is not updated. What could be the problem here?

declare @dd table(Id int,number int, TypeId int)
declare @tt table(Id int,TypeId1 int, TypeId2 int)
insert into @dd values(25,10, 1),(25,15, 2)
insert into @tt values(25,0,0)

update t set 
t.TypeId1=case when TypeId=1 then number end,
t.TypeId2=case when TypeId=2 then number end
from @tt t inner join @dd d on d.Id=t.Id

select * from @tt;

After executing the above code and selecting data from @tt I get the following result:

Id  TypeId1 TypeId2
-------------------
25    10      NULL

which is not acceptable because the value of TypeId2 is null, it should be 15.

Answer 1

Check:

UPDATE @tt
SET @tt.TypeId1 = t.TypeId1, @tt.TypeId2 = t.TypeId2
FROM @tt
JOIN ( SELECT Id, 
              MAX(CASE WHEN TypeId=1 THEN number END) TypeId1,
              MAX(CASE WHEN TypeId=2 THEN number END) TypeId2
       FROM @dd
       GROUP BY Id ) t
ON @tt.Id = t.Id