aggregation in UPDATE not allowed

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

Transact-SQL (2000)

aggregation in UPDATE not allowed

Author

Topic

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-04-20 : 05:56:54

UPDATE Target
SET Target.Col1 = SUM(Source.Col1 - Source.Col2)
FROM source S
INNER JOIN target T
ON S.PK = T.PK

I can do this with temp tables or cursors, but surely there must be a better/faster way?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-04-20 : 06:03:10

UPDATE Target
SET Target.Col1 = Source.Col1 - Source.Col2
FROM source S
INNER JOIN target T
ON S.PK = T.PK

You are misinterpreting the objectives of the SUM function.

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-04-20 : 06:25:01

The target is a summary table and the source is a details table.
How do do I store for each summary (Sale) the sum profit which is the sum of all the "saleprice - costprice" for each detail in a sale?

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2005-04-20 : 06:36:42

[code]
update Target
set Target.Col1 = a.Total
from (select Total = sum(Col1)-sum(Col2), PK
from Source
group by PK) as a
where a.PK = Target.PK
go
[/code]

------------->>> BREAKING NEWS!!! <<<-------------