Update Using Case Clause

Question

How can I perform an update using a case clause based on the following query using SQL in SQL Management Studio:

SELECT 
CASE WHEN t1.building is null THEN 0
ELSE t1.building END AS Building,
t1.id, 
CASE WHEN t1.building is null THEN 0
ELSE t2.count END AS Count
FROM table t1
JOIN (SELECT building, COUNT(*) as count 
  FROM table 
  GROUP BY building) AS t2 ON t2.building = t1.building OR (t2.building is 
  null AND t1.building is null)

The following does not work:

Update table
Set count=(Select count from table where count in( 
SELECT 
CASE WHEN t1.building is null THEN 0
ELSE t1.building END AS Building,
t1.id, 
CASE WHEN t1.building is null THEN 0
ELSE t2.count END AS Count
FROM table t1
JOIN (SELECT building, COUNT(*) as count 
  FROM table 
  GROUP BY building) AS t2 ON t2.building = t1.building OR (t2.building is 
  null AND t1.building is null))

My objective is to update the count column with values based off the original query per id number.

From:

ID  Building  Count
1    10        
2    10        
3    11        
4    11        
5    11        
6    Null      

Desired Results:

ID  Building  Count
1    10        2
2    10        2
3    11        3
4    11        3
5    11        3
6    Null      0

Answer 1

You can try to use JOIN UPDATE with CASE WHEN

UPDATE t1
SET Count = CASE WHEN t1.Building IS NULL THEN 0 ELSE t2.cnt END
FROM T t1
LEFT JOIN (
  select Building,COUNT(Building) cnt
  from T
  GROUP BY Building
) t2 on t1.Building = t2.Building 

sqlfiddle