pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUpdating column to link one table to another
2+ messages / 2 participants
[nested] [flat]
* Updating column to link one table to another
@ 2004-12-15 10:07 Mark Collette <mark@hyperbyte.ab.ca>
0 siblings, 1 reply; 2+ messages in thread
From: Mark Collette @ 2004-12-15 10:07 UTC (permalink / raw)
To: pgsql-sql
I have two tables, with a many to one relationship.
Table InfrequentTable
- timestamp ts
- int infrequentId (newly added key)
Table FrequentTable (Many FrequentTable entries per one
InfrequentTable entry)
- timestamp ts
- int infrequentId (newly added column)
The link is chronological, in that when an InfrequentTable entry
happens, then subsequent FrequentTable entries should be linked to it,
until the next InfrequentTable event happens, in which case old
FrequentTable entries are left alone, but new ones are linked to the
newest InfrequentTable entry.
Now, I've added the infrequentId columns to both, as an optimization,
so that I can quickly find the InfrequentTable entry for a given
FrequentTable entry. I've done this because I want to speed up
SELECTs. Any new INSERTs are working just fine. But, all my legacy
entries, are not linked up yet. I need to know how I can UPDATE the
FrequentTable rows, where their infrequentId is zero, to point at the
last InfrequentTable entry whose timestamp ts is before its own
timestamp ts.
Can anyone help me with this? Thank you.
- Mark Collette
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Updating column to link one table to another
@ 2004-12-20 09:13 Richard Huxton <dev@archonet.com>
parent: Mark Collette <mark@hyperbyte.ab.ca>
0 siblings, 0 replies; 2+ messages in thread
From: Richard Huxton @ 2004-12-20 09:13 UTC (permalink / raw)
To: Mark Collette <mark@hyperbyte.ab.ca>; +Cc: pgsql-sql
Mark Collette wrote:
>
> Now, I've added the infrequentId columns to both, as an optimization, so
> that I can quickly find the InfrequentTable entry for a given
> FrequentTable entry. I've done this because I want to speed up
> SELECTs. Any new INSERTs are working just fine. But, all my legacy
> entries, are not linked up yet. I need to know how I can UPDATE the
> FrequentTable rows, where their infrequentId is zero, to point at the
> last InfrequentTable entry whose timestamp ts is before its own
> timestamp ts.
How do you distinguish between InfrequentTable entries when their
timestamp is the same?
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-12-20 09:13 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-12-15 10:07 Updating column to link one table to another Mark Collette <mark@hyperbyte.ab.ca>
2004-12-20 09:13 ` Richard Huxton <dev@archonet.com>
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