Hi 

I'm trying to accomplish the following update. What is the correct and most efficient way to do this?

• I want to update #tmp2 with the price in #tmp
• Where the date in #tmp is equal to the date in #tmp2
• If there is no date then use the latest date from #tmp.

the two tables join on #tmp.pkiProdId = #tmp2.fkiProdID

create table #tmp (pkiProd int,dteDate datetime,numPrice decimal (18,2))
insert into #tmp select 1,'2013-01-01',1
union all
select 1,'2013-01-02',1.5
union all 
select 1,'2013-01-03',1.7
union all 
select 1,'2013-01-04',1.9
union all 
select 1,'2013-01-05',2
union all 
select 1,'2013-01-06',2.4
union all 
select 1,'2013-01-07',3.4


create table #tmp2 (pkiTransID int identity, fkiProdID int,dteDate DATETIME,numPrice DECIMAL (18,2))
insert into #tmp2 (fkiProdID,dteDate)
select 1,'2013-01-02'
union all
select 1,'2013-01-05'
union all 
select 1,'2013-01-09'

update #tmp2
set numPrice = #tmp.numPrice
from #tmp inner join #tmp2 on (#tmp.pkiProd = #tmp2.fkiProdID)
where #tmp2.dteDate = #tmp.dteDate or #tmp.dteDate < #tmp2.dteDate --?

select *from #tmp2

drop table #tmp
drop table #tmp2

• Edited by LVE7 2 hours 46 minutes ago

May be the below:

create table #tmp (pkiProd int,dteDate datetime,numPrice decimal (18,2))
insert into #tmp select 1,'2013-01-01',1
union all
select 1,'2013-01-02',1.5
union all 
select 1,'2013-01-03',1.7
union all 
select 1,'2013-01-04',1.9
union all 
select 1,'2013-01-05',2
union all 
select 1,'2013-01-06',2.4
union all 
select 1,'2013-01-07',3.4


create table #tmp2 (pkiTransID int identity, fkiProdID int,dteDate DATETIME,numPrice DECIMAL (18,2))
insert into #tmp2 (fkiProdID,dteDate)
select 1,'2013-01-02'
union all
select 1,'2013-01-05'
union all 
select 1,'2013-01-09'


update A Set A.numPrice = B.numPrice
From #tmp2 A
Inner join #tmp 
 B on A.fkiProdID = B.pkiProd and A.dteDate = B.dteDate

;With cte
as
(
	Select B.pkiProd,B.dteDate MaxDate,B.numPrice ,ROW_NUMBER()Over(partition by B.pkiprod order by B.dtedate desc) Rn 
	From #tmp2 A
	Inner join #tmp B on A.fkiProdID = B.pkiProd and A.dteDate>B.dteDate
	Where A.numPrice is null
) update A Set A.numPrice = B.numPrice
From #tmp2 A
inner join cte B On A.fkiProdID = B.pkiProd
where Rn=1 and A.numPrice is null

select *from #tmp2

drop table #tmp
drop table #tmp2

LVE7, Use below Code.

DECLARE @DateVariable DATETIME
SET @DateVariable = (SELECT MAX(dteDate)FROM #tmp)

UPDATE TT
SET TT.Price=T.Price
FROM #tmp T RIGHT JOIN #tmp2 TT ON T.pkiProdID=TT.fkiProdID
WHERE TT.dteDate=ISNULL(T.dteDate,@DateVariable) 

Pardon me for any typos.

Does  your query perform correct data? your query looks ok.