CODEWITHSUNDEEP
sqlsql-server
Hamed
Hamed

Reputation: 11

"An aggregate may not appear in the set list of an UPDATE statement". Performing 2 SQL queries together

I have a table in my SQL Server which contains columns

Currency, Amount, CurrencyRequestedTotal, Pcs

The table looks like this:

ID  Currency     Amount     Pcs         CurrencyRequestedTotal
--------------------------------------------------------------
1   USD          50000.00   4           NULL
2   NGN          30000.00   5           NULL
3   USD          49800.00   6           NULL
4   NGN          2500.00    6           NULL

I have these 2 SQL queries I wrote so far, one is to do the calculation and the second one is then to update the table with the ID using the Update query

It looks like this :

SELECT
    Currency, SUM(Amount) 
FROM
    [dbo].[CurrencyStats_SMO] 
WHERE
    Currency = 'NGN' 
GROUP BY 
    Currency

UPDATE [dbo].[CurrencyStats_SMO] 
SET CurrencyRequestedTotal = SUM(Amount) 
WHERE ID = 1

On the console, it does not show anything that looks like errors, but on the second note I get this

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

Like what exactly do I appear to be missing?

Upvotes: 1

Views: 1015

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270583

SQL Server supports window functions and updatable CTEs. I would recommend:

with toupdate as (
      select cs.*,
             sum(case when Currency = 'NGN' then amount end) over (partition by id) as sum_amount        
      from CurrencyStats_SMO cs
      where id = 1
    )
update toupdate
    set CurrencyRequestedTotal = sum_amount;

The above sets the total for all currencies to the same value. If you want each currency to have its own total, then the query would look like:

with toupdate as (
      select cs.*,
             sum(amount) over (partition by id, currency) as sum_amount        
      from CurrencyStats_SMO cs
      where id = 1
    )
update toupdate
    set CurrencyRequestedTotal = sum_amount;

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You need subquery : 

update smo
       set smo.CurrencyRequestedTotal = (select sum(smo1.Amount) 
                                         from [dbo].[CurrencyStats_SMO] smo1
                                         where smo1.Currency = smo.Currency
                                        )
from [dbo].[CurrencyStats_SMO] smo
where smo.Currency = 'NGN';

Upvotes: 1

Related Questions