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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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