|
|
SQL Server Error Messages - Msg 157
Error Message
Server: Msg 157, Level 15, State 1, Line 1
An aggregate may not appear in the set list of an
UPDATE statement.
Causes
As the message suggests, this error occurs when you are trying to set one of your columns using the UPDATE statement with a value that uses an aggregate function such as the SUM,
|
To illustrate, let's assume you have the following 2 tables:
CREATE TABLE [dbo].[Account Balance] (
[Account Number] VARCHAR(10),
[Account Balance] MONEY,
[Last Transaction Date] DATETIME
)
CREATE TABLE [dbo].[Account Transaction] (
[Account Number] VARCHAR(10),
[Transaction Amount] MONEY,
[Transaction Date] DATETIME
)
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 [dbo].[Account Balance] table using the following UPDATE statement:
UPDATE A
SET [Account Balance] = SUM([Transaction Amount]),
[Last Transaction Date] = MAX([Transaction Date])
FROM [dbo].[Account Balance] A INNER JOIN [dbo].[Account Transaction] B
ON A.[Account Number] = B.[Account Number]
Issuing this UPDATE statement will generate the following error message:
Server: Msg 157, Level 15, State 1, Line 2
An aggregate may not appear in the set list of an UPDATE statement.
Solution / Work Around
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]
Alternatively, you can also use a common-table expression (CTE) instead of a sub-query. Here's how the query will look like using a CTE:
WITH AccountBalanceCTE AS (
SELECT [Account Number], SUM([Transaction Amount]) AS [Account Balance],
MAX([Transaction Date]) AS [Last Transaction Date]
FROM [dbo].[Account Transaction]
GROUP BY [Account Number])
UPDATE A
SET [Account Balance] = B.[Account Balance],
[Last Transaction Date] = B.[Last Transaction Date]
FROM [dbo].[Account Balance] A INNER JOIN AccountBalanceCTE B
ON A.[Account Number] = B.[Account Number]
|