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

One of our trainee programmers had written this query in SQL Server:

  --UPDATE SORT ORDER TO 1 + (the current max value)

    UPDATE [My_Question] SET SortOrder = MAX(SortOrder) + 1

    WHERE QuestionID = @QuestionID

He wanted to set the Sort Order of this question to the next integer value by checking the maximum value in the list of questions already added.  While trying to run this he got this error:

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

This error comes because in an UPDATE statement you cannot add aggregate methods like MAX, SUM etc. as it will become ambiguous. To fix this, one can simple use variables and edit the statement as follows:

--UPDATE SORT ORDER TO 1 + (the current max value)

    DECLARE @SortOrder INT

    SELECT  @SortOrder = MAX (SortOrder)

    FROM    [My_Question]

    WHERE   QuestionID = @QuestionID

    UPDATE  [My_Question]

    SET     SortOrder = ( @SortOrder + 1 )

    WHERE   QuestionID = @QuestionID