| Aggregate values in an update query [message #372144] |
Mon, 15 January 2001 13:27  |
Daniel
Messages: 47
Registered: February 2000
|
Member |
|
|
I'm trying to do the following, but without success:
UPDATE Table1
SET FieldA = SUM(Table2.FieldX)
WHERE Table1.FieldB = Table2.FieldY
Basically, update a field with summary information from another table. It seems like it should be a no-brainer, but I can't seem to get it to work--I get an illegal use of aggregate function in an update statement error.
|
|
|
|
| Re: Aggregate values in an update query [message #372145 is a reply to message #372144] |
Mon, 15 January 2001 13:35  |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
Try something like:
UPDATE Table1 T1
SET FieldA =
(select SUM(T2a.FieldX)
from Table2 T2a
WHERE T1.FieldB = T2a.FieldY)
where T1.FieldB in
(select T2b.FieldY
from Table2 T2b);
The first sub query is a "correlated subquery"
The second is required to prevent FieldA from getting set to null where the sub query returns no SUM for that row.
|
|
|
|
| Re: Aggregate values in an update query [message #372146 is a reply to message #372145] |
Mon, 15 January 2001 13:57 |
Daniel
Messages: 47
Registered: February 2000
|
Member |
|
|
That was my first attempt, actually. The sub query was not appreciated by the server, which I thought was odd since it works just dandy for SELECT & INSERT queries. I don't know if I'm using an unfriendly flavor of SQL (Teradata, which is SQL ANSI compatable) or what the problem is.
Thanks for the suggestion, though.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
