I have task to find values of integer according to last (max) of timestamp (datetime) value.
Because it is so complicated query I am consindering this way to find last value something like this
update my_table set value=last_value from my_table inner join ( select * from ( select top(100) percent from ( select ts,last_value,pk_v from tb1 union all select ts,last_value,pk_v from tb2 .. ) as temp order by ts ) as temp_order ) as temp on my_table.pk_v=temp.pk_v
The idea is to make sub-query which is ordered by timestamp and then to update table.
In this case, sometime is going to be more values in subquery for one pk_val (primary_key value).
For me now looks like that it is impossible and I am considering to do updates in CURSOR. But before I go on, I would love to hear you option this.
Cleaner question would be: How updates working when in one transaction (select) SQL need to update same row two times ?
*EDIT:1 * Here is sample with data
create table #Table_To_update (pk int not null,last_value int) insert into #Table_To_update select 1,null union all select 2,null union all select 3,null create table #table_as_sub_query (fk int, value int ,ts datetime) insert into #table_as_sub_query select 1,5,'2012-01-01' union all select 1,6,'2012-03-01' union all select 1,2,'2012-04-01' union all select 2,7,'2012-02-01' union all select 2,8,'2012-02-05' union all select 2,6,'2012-04-01' union all select 3,0,'2012-01-01' union all select 3,9,'2012-05-05' union all select 3,12,'2012-01-01' /*--This Way I want to update new table with last values --*/ update #Table_To_update set last_value=table2.value from #Table_To_update table1 inner join (select top(100) percent * from #table_as_sub_query order by ts desc ) as table2 on table1.pk=table2.fk