Skip site navigation (1) Skip section navigation (2)

Updating column to link one table to another

From: Mark Collette <mark(at)hyperbyte(dot)ab(dot)ca>
To: pgsql-sql(at)postgresql(dot)org
Subject: Updating column to link one table to another
Date: 2004-12-15 10:07:03
Message-ID: (view raw, whole thread or download thread mbox)
Lists: 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


pgsql-sql by date

Next:From: Marian POPESCUDate: 2004-12-15 15:10:09
Subject: Join issue?
Previous:From: Josh BerkusDate: 2004-12-15 02:29:57
Subject: Re: Indexing Strategy

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group