Menu
As of my knowledge, No you can not directly use GROUP by as you can not use aggregate functions in an UPDATE query.
An aggregate may not appear in the set list of an UPDATE statement. But SQL doesn't always agree that it should be simple. Let's setup a contrived example using a data set of Airport Gate information from San Francisco Airport. I have 1 table which has all of the times a Gate has been used at the airport.
Updating a View The UPDATE statement can only reference columns from one base table. This means it's not possible to update multiple tables at once using a single UPDATE statement.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With