Thread: Updating column to link one table to another

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

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