错误在SQL Server在更新数据
问题描述:
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
答
改变你的更新语句如下:
update dbo.Assignment_BuyerInfo
set PickupDate=#tempTbl.dt
from dbo.Assignment_BuyerInfo, #tempTbl
where AssignmentID=#tempTbl.AssID
你必须包括你的额外的表在FROM子句,就像你,如果你在做一个select语句。
+0
+1好抓! – 2010-01-14 13:58:04
请用合适的方式编写代码... – anishMarokey 2010-01-14 12:48:51
请发布您也收到的错误消息。 – Doogie 2010-01-14 12:51:05
请编辑您的问题并添加一些有关确切问题的信息。您是否收到错误消息,或者INSERT或UPDATE无法正常工作? – DOK 2010-01-14 12:52:01