Обсуждение: Need help with a query

Hello.

I'm having a problem with  a probably very simple query.
I need to update a table
foo (x int, y int, last_seen timestamp, unique(x,y));

where the values should be taken from a larger table
bar( x int, y int, seen timestamp);
where each x,y combination occurs several times, and the value in
foo.last_seen should be the value max(seen) for each pair of x,y.
Notice! All combinations of x,y in bar are also in foo, but not the
other way around.

So how do I write a query for this?
Thanks in advance.

Howdy,
AB

Please check if this (untested) query produces the result you want.

Best,
Oliver

UPDATE foo
SET last_seen = subquery.maximo
FROM
(
SELECT x,y,MAX(seen) as maximo
FROM bar
GROUP BY x,y
) as subquery
WHERE x = subquery.x
AND y = subquery.y

----- Original Message -----
From: "A B" <gentosaker@gmail.com>
To: <pgsql-novice@postgresql.org>
Sent: Tuesday, October 26, 2010 10:16 AM
Subject: [NOVICE] Need help with a query


> Hello.
>
> I'm having a problem with  a probably very simple query.
> I need to update a table
> foo (x int, y int, last_seen timestamp, unique(x,y));
>
> where the values should be taken from a larger table
> bar( x int, y int, seen timestamp);
> where each x,y combination occurs several times, and the value in
> foo.last_seen should be the value max(seen) for each pair of x,y.
> Notice! All combinations of x,y in bar are also in foo, but not the
> other way around.
>
> So how do I write a query for this?
> Thanks in advance.
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice