Msg 157 - An aggregate may not appear in the set list of an UPDATE

If you are trying to update a column using an aggregate function like SUM, MAX or MIN etc the following error will come.


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


To understand this you try the following example. Suppose you have two tables called ProductMaster and StockTransaction respectively. And ProductMaster table have the fields ProductID, ProductName, Stock, LastTransactionDate. And StockTransaction table have the fields called ProductID, Quantity, TransactionDate


The first table contains all the Products with the current Stock and the last transaction date. The second table contains all the transactions performed on those Products. You are trying to update the Stock and Last Transaction Date columns of your ProductMaster table using the following UPDATE statement:


UPDATE A
SET [Stock] = SUM([Quantity]),
[Last TransactionDate] = MAX([TransactionDate])
FROM [dbo].[ProductMaster] A INNER JOIN [dbo].[StockTransaction] B
ON A.[ProductID] = B.[ProductID]


To update a table with the aggregate values of another table you should use a sub-query:


UPDATE A
SET [Stock] = B.[Quantity],
A.[Last Transaction Date] = B.[Last Transaction Date]
FROM [dbo].[ProductMaster] A INNER JOIN 
(SELECT [ProductID], SUM([Quantity]) AS [Quantity],
MAX([Transaction Date]) AS [Last Transaction Date]
FROM [dbo].[StockTransaction]
GROUP BY [ProductID]) B
ON A.[ProductId] = B.[ProductID]