Re: UPDATE with SUM = possible??

Article View: bit.databases.mssql-l

Article #197

Back to bit.databases.mssql-l Previous Next

#197

From: Kevin Mitchell
Date: Wed, 15 May 2002 05:30

33 lines
2585 bytes

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;


"AGP" <sindizzy.pak@softhome.net> wrote in message
news:p1py8.76489$HH5.4608@rwcrnsc51.ops.asp.att.net...
> 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
>
>
>
>

Technical Details

Message-ID: <9omE8.27140$2m.6391555@typhoon1.se.ipsvc.net>

Path:

arc1-ca.pugleaf.net!archiver1.google.com!news1.google.com!sn-xit-02!sn-xit-01!supernews.com!207.217.77.43.MISMATCH!newsfeed1.earthlink.net!newsfeed.earthlink.net!news-hog.berkeley.edu!ucberkeley!nntp.cs.ubc.ca!cyclone.mw.ipsvc.net!news.mw.ipsvc.net!newsfeed.se.ipsvc.net!typhoon1.se.ipsvc.net.POSTED!not-for-mail

References: <p1py8.76489$HH5.4608@rwcrnsc51.ops.asp.att.net>

Show Full Headers

Complete Article Headers

X-Google-Language: ENGLISH,ASCII-7-bit
X-Google-Thread: 104586,abdb81538e97caf8
X-Google-Attributes: gid104586,public
X-Google-Thread: f6611,abdb81538e97caf8
X-Google-Attributes: gidf6611,public
X-Google-Thread: 103bcf,abdb81538e97caf8
X-Google-Attributes: gid103bcf,public
X-Google-Thread: 105516,abdb81538e97caf8
X-Google-Attributes: gid105516,public
X-Google-Thread: 10be7f,abdb81538e97caf8
X-Google-Attributes: gid10be7f,public
X-Google-ArrivalTime: 2002-05-14 22:43:19 PST
Path: arc1-ca.pugleaf.net!archiver1.google.com!news1.google.com!sn-xit-02!sn-xit-01!supernews.com!207.217.77.43.MISMATCH!newsfeed1.earthlink.net!newsfeed.earthlink.net!news-hog.berkeley.edu!ucberkeley!nntp.cs.ubc.ca!cyclone.mw.ipsvc.net!news.mw.ipsvc.net!newsfeed.se.ipsvc.net!typhoon1.se.ipsvc.net.POSTED!not-for-mail
From: "Kevin Mitchell" <kevinmitchell@attbi.com>
Newsgroups: bit.databases.mssql-l,microsoft.public.ado,microsoft.public.ado.rds,microsoft.public.vb.database,microsoft.public.vb.database.dao
References: <p1py8.76489$HH5.4608@rwcrnsc51.ops.asp.att.net>
Subject: Re: UPDATE with SUM = possible??
Lines: 35
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <9omE8.27140$2m.6391555@typhoon1.se.ipsvc.net>
Date: Wed, 15 May 2002 05:30:45 GMT
NNTP-Posting-Host: 66.177.55.207
X-Complaints-To: abuse@attbi.net
X-Trace: typhoon1.se.ipsvc.net 1021440645 66.177.55.207 (Wed, 15 May 2002 01:30:45 EDT)
NNTP-Posting-Date: Wed, 15 May 2002 01:30:45 EDT

Reply to Parent Message