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

SQL Server 2000 Forums

SQL Server Development (2000)

Aggregate in update

Author

Topic

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-10-27 : 05:59:18

The following update gives me "An aggregate may not appear in the set list of an UPDATE statement." Do you guys know of any workarounds?

UPDATE user SET 
  Balance = Balance - IsNull(SUM(b.Amount), 0) 
FROM transcript a WITH (ROWLOCK) INNER JOIN user b WITH (ROWLOCK)
  ON a.UserID = b.UserID
WHERE a.DescID = @DescID
  AND user.Currency = b.Currency  --> This row might confuse things also
  AND a.UserID < 11
GROUP BY c.Currency

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-10-27 : 06:14:01

This might do the trick allthough I haven't tested it yet:

UPDATE user SET 
  Balance = Balance - IsNull(dt1.SumAmount, 0) 
FROM 
  (SELECT Currency AS MyCurrency, SUM(a.Amount) AS SumAmount
  FROM transcript a WITH (ROWLOCK) INNER JOIN user b WITH (ROWLOCK)
    ON a.UserID = b.UserID
  WHERE a.DescID = @DescID
  GROUP BY Currency) AS dt1
WHERE Currency = dt1.MyCurrency

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-10-27 : 06:29:28

Yup, that did the trick...this query replaces a somewhat ugly cursor and I just love it

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources