Aggregate Functions in an Update Statement

I often need to update a record with an aggregate of another table.

Not that sort of aggregate

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.

GateUsage

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Create a website or blog at WordPress.com

Up ↑