0

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.

1
  • The record in UPDATE may be updated only once. So it is updated using random (in your case - first) source record, and possible update using another record is ignored. You must combine source, then update.
    – Akina
    Oct 16, 2019 at 7:28

1 Answer 1

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
2
  • It works thanks, but what is the reason my method is not working? Oct 16, 2019 at 7:37
  • @AsrarAhmadEhsan I had explained this in a comment to your question.
    – Akina
    Oct 16, 2019 at 7:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.