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