Applications

SQL – An aggregate may not appear in the set list of an UPDATE statement

I have a Project table that contains a couple of fields used for reporting and metrics called FirstActivity, LastActivity, and TotalHours.

I created the following SQL query to update those fields based on some aggregated results from the ProjectHours table:

UPDATE 
    [dbo].[Project]
SET 
    FirstActivity = MIN(ProjectHours.[EntryDate]) 
    , FirstActivity = MAX(ProjectHours.[EntryDate]) 
    , TotalHours = SUM(ProjectHours.[TotalHours])
FROM 
    [dbo].[Project] 
    INNER JOIN [ProjectHours] ON [Project].[ProjectId] = [ProjectHours].[ProjectId]

 

When I executed the above query, I received the following error message:

Msg 157, Level 15, State 1, Line 6
An aggregate may not appear in the set list of an UPDATE statement.

It turns out that when an aggregate statement appears directly in an UPDATE statement, there is some confusion regarding whether the associated WHERE clause qualifies the rows to be updated or the rows on which to apply the aggregate. Microsoft support recommended that the UPDATE statement be formatted as follows:

UPDATE <table name>
SET <column name> = (select count(*) from <table name> where <aggregate condition>)
FROM <table names>
WHERE <update condition>

The problem with that is I would need to call a subquery on all three columns I am updating, rather than using one call to update all three columns, which does not strike me as very performant.

It turns out that you can use aggregates in an UPDATE statement by using this format instead:

;WITH [ProjectHoursTotal] AS ( 
    SELECT 
        ProjectId = [ProjectHours].[ProjectId] 
        , FirstActivity = MIN([ProjectHours].[EntryDate]) 
        , LastActivity = MAX([ProjectHours].[EntryDate]) 
        , TotalHours = SUM([ProjectHours].[TotalHours]) 
    FROM 
        [dbo].[ProjectHours] 
    GROUP BY 
        [ProjectHours].[ProjectId]
)
UPDATE 
    [dbo].[Project]
SET 
    [Project].[FirstActivity] = [ProjectHoursTotal].[FirstActivity] 
    , [Project].[LastActivity] = [ProjectHoursTotal].[LastActivity] 
    , [Project].[TotalHours] = [ProjectHoursTotal].[TotalHours]
FROM 
    [dbo].[Project] 
    INNER JOIN [ProjectHoursTotal] ON [Project].[ProjectId] = [ProjectHoursTotal].[ProjectId]

The semicolon is not a typo.

Super fast and super easy! J

Leave a Reply