Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
sachingovekar
Posting Yak Master
101 Posts |
Posted - 2012-11-16 : 11:37:20
|
i have the following tablecreate table #data1(assetcenter int,hped int,model varchar(20))-- INSERT DATAinsert into #data1 (assetcenter, hped,MODEL)select 0,1,'MODEL DL3' union allselect 4,3 ,'MODEL R7' union allselect 0,null,NULL -- resultselect assetcenter, hped,MODEL,case when assetcenter = 0 and hped > 0 then hped when assetcenter > hped then hped when assetcenter = 0 and hped is null then 1end as resultfrom #data1from the above result i want to overwrite the resulr columnwhere model like '%DL3%' or model like '%R7%' RESULT SHOULD BE 2FINAL OUTPUT---------------------------0 1 MODEL DL3 14 3 MODEL R7 20 NULL NULL 1 |
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-11-16 : 11:44:30
|
I think the easiest way to do this, and perhaps the best, is just wrap it in another select statementSELECT t1.assetcenter,t1.hped,t1.MODEL,CASE WHEN t1.model like '%DL3%' or t1.model like '%R7%' THEN 2 ELSE t1.result ENDFROM(select assetcenter, hped,MODEL,case when assetcenter = 0 and hped > 0 then hped when assetcenter > hped then hpedwhen assetcenter = 0 and hped is null then 1end as resultfrom #data1) t1JimEveryday I learn something that somebody else already knew |
|
|
sachingovekar
Posting Yak Master
101 Posts |
Posted - 2012-11-16 : 11:51:28
|
Thanks Jim |
|
|
sachingovekar
Posting Yak Master
101 Posts |
Posted - 2012-11-16 : 12:27:19
|
Hi jim,Consider I am a result column which is empty. How will I update result colum using above select case.Basically I want to update now instead of select. |
|
|
|
|
|
|
|