SQLTeam.com | Weblogs | Forums

How to write this statement with another way best without repeate team id?

How to write this statement with another way best without repeate team id ?

I work on SQL server 2012 i update two fields depend on every team id

   update dpt
set Package=case when tmp.TeamId=1 then 1 end ,
 PackageDate=case when tmp.TeamId=1 then getdate() end ,
 Parametric=case when tmp.TeamId=2 then 2  end,
 ParametricDate=case when tmp.TeamId=2 then getdate() end ,
 Scribing=case when tmp.TeamId=3 then 3  end,
 ScribingDate=case when tmp.TeamId=3 then getdate() end ,
 Lifecycle=case when tmp.TeamId=4 then 4  end,
 LifecycleDate=case when tmp.TeamId=4 then getdate() end 
from PCN.DocumentPartTeams dpt inner join #Temp tmp on dpt.PartID=tmp.PartID and dpt.DocumentID=tmp.DocumentID
and tmp.Status is null

I need to rewrite statement with another way best as following

because i repeat same condition multi time

i need to write it one time only meaning as below

case when teamid=2

then date=getdata and parametric=2

so how to do that please ?

maybe this? Please test it first?

update dpt
set Package= _value,
 PackageDate= _datevalue ,
 Parametric= _value,
 ParametricDate=_datevalue ,
 Scribing=_value,
 ScribingDate=_datevalue ,
 Lifecycle=_value,
 LifecycleDate=_datevalue
from PCN.DocumentPartTeams dpt 
inner join 
	(select 
          case when tmp.TeamId in (1,2,3,4) then tmp.TeamId  end _value,
	   case when tmp.TeamId in (1,2,3,4) then getdate() end as _datevalue 
	
	from #Temp tmp ) tmp 
  on dpt.PartID=tmp.PartID 
 and dpt.DocumentID=tmp.DocumentID
 and tmp.Status is null

we need to know your schema a little better. Please provide DDL and sample data. Just for laughs, what's wrong with the update statement you provided? What is in your #Temp table and how is it populated? Also, your update statements will update the columns to null if the TeamID doesn't match what you are looking for