pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to update
3+ messages / 2 participants
[nested] [flat]
* How to update
@ 2002-06-03 08:00 Andre Schubert <andre.schubert@km3.de>
0 siblings, 1 reply; 3+ messages in thread
From: Andre Schubert @ 2002-06-03 08:00 UTC (permalink / raw)
To: pgsql-sql
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 :)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: How to update
@ 2002-06-03 13:08 Manfred Koizar <mkoi-pg@aon.at>
parent: Andre Schubert <andre.schubert@km3.de>
0 siblings, 1 reply; 3+ messages in thread
From: Manfred Koizar @ 2002-06-03 13:08 UTC (permalink / raw)
To: Andre Schubert <andre.schubert@km3.de>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: How to update
@ 2002-06-03 13:46 Andre Schubert <andre.schubert@km3.de>
parent: Manfred Koizar <mkoi-pg@aon.at>
0 siblings, 0 replies; 3+ messages in thread
From: Andre Schubert @ 2002-06-03 13:46 UTC (permalink / raw)
To: Manfred Koizar <mkoi-pg@aon.at>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-06-03 13:46 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-06-03 08:00 How to update Andre Schubert <andre.schubert@km3.de>
2002-06-03 13:08 ` Manfred Koizar <mkoi-pg@aon.at>
2002-06-03 13:46 ` Andre Schubert <andre.schubert@km3.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox