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.
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
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.
You can't use an aggregate in an UPDATE
query, for starters - though you didn't include the error message in your original question, I suspect that's what it's telling you.
You'll need to calculate the aggregate before your update
and store the results in a temp table, and then join to that table to do your update
.
精彩评论