UPDATE with SUM = possible?? 
Author Message
 UPDATE with SUM = possible??

I am trying to update a field1 in table1 with the
sum of a field2 in table2. the two tables are related through
dbID. There is a unique dbID in table1 that corresponds
to many dbID's in table2. I cant seem to get a SUM statement to
work in an UPDATE statement. My statement looks like so:

UPDATE table1, table2
SET table1.field1 = SUM(table2.field2)
WHERE (table1.dbID = table2.dbID);

I get an error that says i tried to execute a query that does not
include field1 as part of an aggregate function. what am i doing wrong
and how can i fix it.

tia
AGP



Wed, 13 Oct 2004 11:36:53 GMT  
 UPDATE with SUM = possible??
When you use an aggregate function (Sum) you have to include all other
fields listed in the field list in a GROUP BY Clause.

change your SQL to this and it should work

UPDATE table1, table2
 SET table1.field1 = SUM(table2.field2)
 WHERE (table1.dbID = table2.dbID)
GROUP BY table1.field1;


Quote:
> I am trying to update a field1 in table1 with the
> sum of a field2 in table2. the two tables are related through
> dbID. There is a unique dbID in table1 that corresponds
> to many dbID's in table2. I cant seem to get a SUM statement to
> work in an UPDATE statement. My statement looks like so:

> UPDATE table1, table2
> SET table1.field1 = SUM(table2.field2)
> WHERE (table1.dbID = table2.dbID);

> I get an error that says i tried to execute a query that does not
> include field1 as part of an aggregate function. what am i doing wrong
> and how can i fix it.

> tia
> AGP



Sun, 31 Oct 2004 13:30:45 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Updating using Sums from another Table.

2. Using SUM in SQL UPDATE command

3. AM I missing something - Sum and Cumulative Sum

4. Report showing BOTH Aggregate Sum of Display records and Total Sum by Group

5. How to sum a single fiscal period for monthly expenses and also sum ytd expenses

6. Self-updating VB App...is it possible?

7. Self updating VB app...is it possible?

8. Possible WSH 5.6 beta update...

9. Possible to scroll to the updated record

10. Are database UPDATES possible in Crystal Reports ?

11. Possible VB SP Update?

 

 
Powered by phpBB® Forum Software