Msg 157 - An aggregate may not appear in the set list of an UPDATE statement.,sql,server

If you are trying to set one of your columns using an UPDATE statement which uses 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 know this you jist try the following example. Suppose you have two tables called AccountBalance and AccountTransaction respectively. And AccountBalance table have the fields AccountNumber,AccountBalance,LastTransactionDate. And AccountTransaction table have the fields called AccountNumber,TransactionAmount,TransactionDate


The first table contains all the accounts in your company with the current account balance and the last transaction date. The second table contains all the transactions performed on those accounts. You are trying to update the Account Balance and Last Transaction Date columns of your Account Balance table using the following UPDATE statement:

UPDATE A
SET [AccountBalance] = SUM([TransactionAmount]),
[Last TransactionDate] = MAX([TransactionDate])
FROM [dbo].[AccountBalance] A INNER JOIN [dbo].[AccountTransaction] B
ON A.[AccountNumber] = B.[AccountNumber]

To update a table with the aggregate values of another table you have to use a sub-query as can be seen from the following UPDATE statement:

UPDATE A
SET [Account Balance] = B.[Account Balance],
[Last Transaction Date] = B.[Last Transaction Date]
FROM [dbo].[Account Balance] A INNER JOIN
(SELECT [Account Number], SUM([Transaction Amount]) AS [Account Balance],
MAX([Transaction Date]) AS [Last Transaction Date]
FROM [dbo].[Account Transaction]
GROUP BY [Account Number]) B
ON A.[Account Number] = B.[Account Number]