Is it necessary that this is a table? Because it seems a lot like a view of the same data actually. CREATE VIEW ...
The expected UPDATE query for a table can be problematic. I have a similar issue in 1 of my DB's. New records - new x, y - get written to bar and do not yet have a corresponding x, y record in foo. So when the UPDATE runs it ignores the new records. One can have an after insert trigger to keep foo up to date with bar. But such machinations arise because foo is only a view of the same data in bar.
----- Reply message -----
From: "A B" <gentosaker@gmail.com>
Date: Tue, Oct 26, 2010 6:16 am
Subject: [NOVICE] Need help with a query
To: <pgsql-novice@postgresql.org>
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