SQL: Update with value from other table depending on timestamp

Question

Hi,

I have the following problem: I want to update the column "UPDATE_VALUE" of table A with values from the column "SOURCE_VALUE" from another table B. Both tables have a timestamp column, and the value that should be updated in table A should be the one of the row of table B with the highest timestamp smaller/equal than the timestamp of the row of table A.

For example:

Table A:

|  ID  |       TIMESTAMP      |  UPDATE_VALUE |  
|   1  |  2010-09-23 10:00:00 |               |  
|   2  |  2010-09-25 05:20:00 |               |  
|   3  |  2010-09-25 18:10:30 |               |  
|   4  |  2010-09-27 07:04:30 |               |  

Table B:

|  ID  |       TIMESTAM P     |  SOURCE_VALUE  |  
|   1  |  2010-09-23 00:00:00 |     VAL_1     |  
|   2  |  2010-09-24 12:30:00 |     VAL_2     |  
|   2  |  2010-09-24 04:50:00 |     VAL_3     |  
|   3  |  2010-09-25 12:00:00 |     VAL_4     |  
|   3  |  2010-09-25 12:20:00 |     VAL_5     |  
|   4  |  2010-09-26 01:50:00 |     VAL_6     | 
|   4  |  2010-09-26 02:00:00 |     VAL_7     |  
|   5  |  2010-09-27 14:00:00 |     VAL_8     |  
|   6  |  2010-09-28 22:00:00 |     VAL_9     |  

So in row 1 of table A the I want VAL_1 of table B as the timestamp of this row is the highest of table B that is smaller/equal than the timestamp of the row in table A. For row 2 of table A I want row 4 of table B and so on. The complete result should look like this:

Table A:

|  ID  |       TIMESTAMP      |  UPDATE_VALUE |  
|   1  |  2010-09-23 14:25:00 |     VAL_1     |  
|   2  |  2010-09-25 12:00:00 |     VAL_4     |  
|   3  |  2010-09-25 14:00:00 |     VAL_5     |  
|   4  |  2010-09-27 16:50:00 |     VAL_8     |  

I am using SQLite 3, but any hints on the sql-query is appreciated.

Thanks for your time and help,

Sugi

Answer 1

Your Update TableA has some timestamp values which are not in TableA and TableB?

But just to update on timestamp following should work.

update TableA set update_value = tb.source_value
from TableA ta inner join TableB tb on ta.TimeStampColumn = tb.TimeStampColumn

Answer 2

I don't know in SQLite but in SQL Server it will be like this:

update tablea set update_value = (select top 1 source_value from tableb where tablea.timestamp<=tableb.timestamp order by tableb.timestamp desc)