Error in SQL Server while updating data

Question

Create Table #tempTbl
(
 [AssID] [int],
 [dt] [datetime]
)

insert into #tempTbl 
select distinct(AssignmentID), (case when isdate(substring(NoteText,len('Vehicle picked-up was completed on')+1,len(NoteText)))=1 then
        substring(NoteText,len('Vehicle picked-up was completed on')+1,len(NoteText)) else '01/01/1900' end) as dop
from dbo.Assignment_Notes
where
    AssignmentID in(Select AssignmentID from dbo.Assignment_ClaimInfo 
        where InsuranceComp='Access General')
and
    AssignmentID in (Select AssignmentID from dbo.Assignment_BuyerInfo where PickupDate is null)
and
    NoteTypeID=5

update dbo.Assignment_BuyerInfo 
set PickupDate=#tempTbl.dt 
where AssignmentID=#tempTbl.AssID

Answer 1

change your update statement to the following:

update dbo.Assignment_BuyerInfo 
   set PickupDate=#tempTbl.dt 
  from dbo.Assignment_BuyerInfo, #tempTbl
 where AssignmentID=#tempTbl.AssID

You have to include your extra table in a from clause just like you would if you were doing a select statement.