SQL Server Error Msg 157 – An aggregate may not appear in the set list of an UPDATE statement

In this blog post, let’s learn about the error message “157 – An aggregate may not appear in the set list of an UPDATE statement.” in Microsoft SQL Server, the reason why it appears and the solution to fix it.

SQL Server Error Message

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

Reason for the Error

The SQL Server Error Msg 157 “An aggregate may not appear in the set list of an UPDATE statement” occurs when you try to use an aggregate function (such as SUM, AVG, MIN, MAX, COUNT) in the SET clause of an UPDATE statement.

Here is an example using the AdventureWorks2019 database that demonstrates the error:

UPDATE Sales.SalesOrderDetail 
SET UnitPrice = SUM(UnitPrice)
WHERE SalesOrderID = 43659

This UPDATE statement tries to set the value of the UnitPrice column to the sum of the UnitPrice values for the specified sales order. However, this is not allowed because aggregate functions are intended to perform calculations on a group of rows and return a single value, not to set the value of a column in a specific row.

Solution

To correct this error, you should modify the UPDATE statement to use a subquery that calculates the aggregate value and returns it as a scalar value. Here’s an example of how you could modify the previous statement:

UPDATE Sales.SalesOrderDetail 
SET UnitPrice = (SELECT SUM(UnitPrice) FROM Sales.SalesOrderDetail WHERE SalesOrderID = 43659)
WHERE SalesOrderID = 43659

This updated statement uses a subquery to calculate the sum of the UnitPrice values for the specified sales order and returns it as a scalar value, which can then be used to set the value of the UnitPrice column for that specific row.