0

I'm trying to write an update statement in Oracle that will find an attribute from a history table based on a timestamp. So, for example the data looks like:

TABLE A
A_ID  TIMESTAMP              ATTR
---------------------------------
1     5/27/2012 10:30:00 AM  ?

TABLE B
B_ID  A_ID  TIMESTAMP              ATTR
---------------------------------------
1     1     5/26/2012 9:01:08 AM   W
2     1     5/27/2012 8:38:21 AM   X
3     1     5/28/2012 9:01:01 AM   Y
4     1     5/29/2012 11:37:54 PM  Z

The lower bound is >= B.TIMESTAMP, but I'm not sure how to write the upper bound as < B."the next TIMESTAMP". So, in the example above the attribute on table A should update to "X".

This seems like a fairly common use case. I've seen this post, but it looks like a satisfactory answer was never reached, so I thought I'd post again.

Community
  • 1
  • 1
Paul
  • 3,725
  • 12
  • 50
  • 86
  • you first have to emit a written clear rule in a sentence, before you actually could start building a query. What is the definition of the candidate record from TABLE B? – Sebas May 30 '12 at 15:30
  • The rule is: find the record in TABLE B where TABLE A's timestamp is >= TABLE B's timestamp, but < TABLE B's next timestamp, unless it is the last timestamp and the FK (A_ID) matches. Does that make sense? – Paul May 30 '12 at 15:33

1 Answers1

0
UPDATE A SET attr = (
    SELECT b1.attr 
    FROM B b1
        INNER JOIN (
            SELECT MAX(b3.timestamp) mx FROM B b3
            WHERE b3.timestamp < A.timestamp
        ) b2 ON b1.timestamp = b2.mx
)

I can't remember if Oracle will allow me to use table A within the inner join sub query... Would you mind trying it?

Sebas
  • 21,192
  • 9
  • 55
  • 109
  • The join was allowed, but did not pass all my test cases. I'll play around with it and see if I can find the issue. – Paul May 30 '12 at 15:54
  • Could you give me more informations please? What went wrong (example please)? – Sebas May 30 '12 at 15:55
  • Not every timestamp on the inner select that should have returned a value did. I have a more complex data set for testing. – Paul May 30 '12 at 16:00