SQL Server Update Group by

I'm trying to execute this on MS-SQL but returns me an error just at the Group by line

update #temp
Set Dos=Count(1)
From Temp_Table2010 s
where Id=s.Total and s.total in (Select Id from #temp)
group by s.Total

Do anyone knows how can I solve this problem having good performance.

Solution 1:

Try

;with counts 
AS 
( 
    SELECT total, COUNT(*) as dos
    FROM temp_table2010 
    WHERE total in (select id from #temp)
)
UPDATE T 
SET dos=counts.dos
FROM #temp T 
INNER JOIN counts 
    ON t.id = counts.total 

Solution 2:

In SQL Server you can do aggregation in an update query you just have to do it in a subquery and then join it on the table you want to update.

UPDATE  #temp
SET     Dos = Cnt
FROM    #temp 
    INNER JOIN (SELECT Total, COUNT(*) AS Cnt FROM Temp_Table2010 GROUP BY Total) AS s
        ON Id = s.Total 

Doing this:

WHERE total in (select id from #temp)

And then:

 INNER JOIN counts 
    ON t.id = counts.total 

Is redundant.

The join solves the "total in (...)" requirement. Group on the key and then join.