Aggregate Functions in an Update Statement
I often need to update a record with an aggregate of another table.
It’s often a parent record that has a record of the sum of a column in all of it’s child records. It seems like a really simple case of having an update statement and setting the value of the column to the sum of the child records.
Msg 157, Level 15, State 1, Line 17
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.
Let’s say I want to have a table that records the last time a gate was used (it’s a contrived example, go with it). I might create the table like this:
And then populate it with a simple insert statement using the MAX aggregate:
Now assume that a whole heap more data is inserted into the table and I want to go and update my aggregate table to show the new Maximum recorded time for each gate. How would I do that? My first guess might be to just create an update statement in the same way as I created the insert:
But SQL won’t let me:
Msg 157, Level 15, State 1, Line 17
An aggregate may not appear in the set list of an UPDATE statement.
I haven’t been able to find a reason why you can’t use an aggregate function in an update, but I suspect it’s due to needing to group part of the query and not another part, and there would be many times when it would be ambiguous to guess which part to aggregate. MSDN does make it clear that there is no GROUP BY clause for an update statement, and their example code using aggregates never select directly from the table, they use sub queries or CTE’s.
Personally, I like the CTE approach better as it allows you to write your query once and aggregate multiple data instead of having to write multiple sub queries to aggregate each piece of data. CTE’s are also easy to break into their own temporary table if your logic starts to get too complex.
Putting it in a CTE and then joining to it will make your update statement work.
If you know the reason for why you can’t use an aggregate in an update, I’d love to hear from you in the comments.
James
May 5, 2020, 2:46 amHi, did you ever find out why you can’t use an aggregate in an update?
Greg
May 5, 2020, 8:04 amIt is because of the order that SQL executes the query. To get the data, it will need to aggregate, meaning you no longer have the original rows to update, so it fails. There are a few other ways around this without a CTE like inline SQL (although number of executions could go through the roof), or join to a sub-select (essentially the same as CTE in this case).
Sumeet Moray
Feb 2, 2023, 6:34 pmHey i want to thank you … your update CTE Expression has really helped us … in our project ! … we used this to generate ratings for vendors and markets … based on order ratings !