Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
                        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
share|improve this question
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
show 2 more comments

1 Answer

up vote 3 down vote accepted

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,
  pk_vv
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
share|improve this answer
    
+1 - I think this is what the OP is after. –  JNK May 9 '12 at 12:43
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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