Thread: How to update

Hi all,

i have a simple problem with an sql-update.
Lets say i have a table foo which contains a
ip::inet and to counters inet and local.
And i have a table bar which contains
ip::inet inet and local.

I need these two tables for traffic-accounting.
The table foo should contain the sum of traffic of bar,
therefore once a month i would like to run a script which 
performs a query that
selects sum(inet),sum(local) from bar
and
updates foo with these two values where bar.ip = foo.ip.

Who can explain how to perform this action with one query.
I tried this, but it update always one row in foo.

update foo set inet=sum(bar.inet),local=sum(bar.local) where foo.ip = bar.ip;

Thanks in advance

P.S.: Sorry for my bad english, hope you understand what i mean :)

On Mon, 3 Jun 2002 10:00:28 +0200, Andre Schubert
<andre.schubert@km3.de> wrote:
>I tried this, but it update always one row in foo.
>
>update foo set inet=sum(bar.inet),local=sum(bar.local) where foo.ip = bar.ip;

Andre,
this worked in my test:

UPDATE foo  SET inet=b.i, local=b.l FROM (SELECT ip, sum(inet) AS i, sum(local) AS l         FROM bar       GROUP BY
ip)bWHERE foo.ip = b.ip;
 

ServusManfred

On Mon, 03 Jun 2002 15:08:46 +0200
"Manfred Koizar" <mkoi-pg@aon.at> wrote:

> On Mon, 3 Jun 2002 10:00:28 +0200, Andre Schubert
> <andre.schubert@km3.de> wrote:
> >I tried this, but it update always one row in foo.
> >
> >update foo set inet=sum(bar.inet),local=sum(bar.local) where foo.ip = bar.ip;
> 
> Andre,
> this worked in my test:
> 
> UPDATE foo
>    SET inet=b.i, local=b.l
>   FROM (SELECT ip, sum(inet) AS i, sum(local) AS l
>           FROM bar
>         GROUP BY ip) b
>  WHERE foo.ip = b.ip;
> 
> Servus
>  Manfred

Thank you very much, this always works for me... :)

Regards