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

Pseudo code:

update my_table
    set value=last_value
from my_table 
    inner join (
                 select *
                    from (
                    select top(100) percent
                            ( 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
  • 4
    How do you want to update the same row twice? Can you explain a little more of what your goal is, including source data and desired output? – JNK May 8 '12 at 19:59
  • @JNK I added sample, Can You now understan my poor english – adopilot May 9 '12 at 7:30
  • That makes it a little clearer. Do you just want the newest value for each PK? – JNK May 9 '12 at 12:44
  • @JNK Yes I do , request is to find newes value, – adopilot May 9 '12 at 13:11
  • 1
    If there are two matches in the table to be updated, both get updated. If there are two in the SOURCE table, the CTE will reduce it to a single value (the newest one). – JNK May 9 '12 at 13:22

Your question is not clear. You are trying to update the value of some table based on a value of another? This can be solved using something like the query below:

In SQL Server 2005 or newer:

with my_data_cte (
  select row_number() over (order by timestamp desc) as sequence,
from (
  select timestamp, pk_v from tb1
  union all
  select timestamp, pk_v from tb2
update my_table
set value = pk_v
from my_table mt
  inner join my_data_cte ct on mt.pk_v = mt.pk_v
where sequence = 1
  • FYI you need to use UPDATE mt since you alias it in the FROM, otherwise it won't parse. – JNK May 9 '12 at 13:18

Your Answer

