where rr.ticket in (select ticket from #tempAS_trans)
and rr.ticket = tt.ticket
and rr.when_date <= @as_of_date
group by rr.ticket
having max(rr.when_date) = rr.when_date)
where EXISTS (select distinct rr.ticket from tableA rr where rr.ticket =
Is there a way that I can get it to update multiple rows and have the nested query return only one row. I'm assuming I need a way of telling the subquery which row is currently being looked at? Or something similar?
It is difficult to understand from the code what you are trying to do
Can you show some sample data and the expected result
Why do you use a temp table, you probably don't need it
set rate = rr.rate
from tableA rr, #tempAS_trans tt
where rr.ticket = tt.ticket
from tableA mm
where mm.ticket = rr.ticket
and mm.when_date <= @as_of_date)
So in the situation above I want to update #tempAS_trans table with the appropriate rate from tableA. If @as_of_date is 19/10/2007 then the rate selected for 1a should be 0.2, for 1b the rate should be 0.15 and for 1c it should be 0.22.
So what I need to do is select the rate from the row that has a when_date <= @as_of_date (so if there are 2 rows that meet this criteria I only want to select the most recent of the 2).