no aggregate in update - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

no aggregate in update

Author

Topic

seeker62
Starting Member

40 Posts

Posted - 2012-11-12 : 17:54:45

I have an insert statement with about 40 columns in the select statement. I need to have one of the columns be filled with calculated data. I thought i would insert the data and then do an update i.e.

update Table
set column = sum(columna - columnb)

but that can not be done because aggregates are not allowed in set statement. Can I get around this without putting my 40 columns in the insert statement into the group by segment.

Thanks for the input

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-11-12 : 18:11:01

Not really sure how to advise you with the information you provided. However, I could guess that you need to use a deriuved table (or inline view) or an outter apply in order to do the update. Here is an example of one way you might do that:

UPDATE T
    SET Column = D.SumColumn
FROM 
    TableName AS T
INNER JOIN
    (
        SELECT ID, SUM(columna - columnb) AS SumColumn
        FROM TableName
        GROUP BY Column_list
    ) AS D
    ON T.ID = D.ID

If that doesn't help, here are some links that can help you provide more information:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

seeker62
Starting Member

40 Posts

Posted - 2012-11-13 : 09:52:13

Thank you that did the trick. New problem though the sum is not giving the correct output.

set columnc = sum(columnb - columna)

if
columnb = 105800 and columna = 40000 then columnc should be 65800 but it is 180440

what aggregate calculation function should i be useing

Got it fixed just used simple columnb - columna in insert statement

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources