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, 2012 at 19:59
  • @JNK I added sample, Can You now understan my poor english
    – adopilot
    May 9, 2012 at 7:30
  • That makes it a little clearer. Do you just want the newest value for each PK?
    – JNK
    May 9, 2012 at 12:44
  • @JNK Yes I do , request is to find newes value,
    – adopilot
    May 9, 2012 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, 2012 at 13:22

1 Answer 1


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, 2012 at 13:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.