SQL Server Update Group by

sql group-by sql-update
Gerardo Abdo picture Gerardo Abdo · May 18, 2010 · Viewed 80.3k times · Source

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.

Answer

Code Magician picture Code Magician · Aug 8, 2011

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

Related questions

Using group by on multiple columns

I understand the point of GROUP BY x. But how does GROUP BY x, y work, and what does it mean?

Read More
Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
Retrieving the last record in each group - MySQL

There is a table messages that contains data as shown below: Id Name Other_Columns ------------------------- 1 A A_data_1 2 A A_data_2 3 A A_data_3 4 B B_data_1 5 B B_data_2 6 C C_data_1 If I run a query select * …

Read More