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]
very very helpful.. whoevr u are.. tnx a bunch!! tonight i can sleep =)
ReplyDeleteyes, thank you
ReplyDeleteTruly helpful. Thanks a lot. It was explained lucidly as well. :)
ReplyDeleteThanks a bunch
ReplyDeleteDude...you rock. Thanks so much.
ReplyDeleteThanks a lot!
ReplyDeleteThanks. Effective solution for this problem.
ReplyDeleteStill trying to understand your code but i think this will work. Thanks in advance dude.
ReplyDeleteSimple and Effective...thanks for sharing your solution - Suchitra
ReplyDeleteThx a lot
ReplyDelete