tsql : Access query to TSQL union update conversion correct?

Question

我在访问中有一个查询，我需要将其转换为 sql server 2005 中的存储过程。
access中查询如下:

UPDATE 
tblitem, 
tblFileSignature 
SET 
tblitem.strFileProcesstype = [tblFileSignature].[STRFILEPROCESSTYPE], tblitem.strFileSignatureType = [tblFileSignature].[strfilesignaturetype]
WHERE 
(((tblitem.strFileSignatureType) Is Null) AND 
((tblitem.strFileExclude)="n") AND 
((InStr([tblitem].[strfilesignature],[tblFileSignature].[strsignature]))=1) AND ((tblitem.uidItemType)=1 Or (tblitem.uidItemType)=5) AND 
((tblitem.uidCollection)=[forms]![frmSetup]![txtInputCol]) AND ((tblitem.strFileSignature) Not Like "d0c*") AND 
((tblFileSignature.strFileProcessType) Not Like "ZIP"));

在 tsql 中..这会一样吗？

update tblItem 
set 
i.strFileProcesstype = f.strFileProcesstype,
i.strFileSignatureType = f.strfilesignaturetype 

from tblItem as I UNION tblFileSignature as F 

WHERE (((i.strFileSignatureType) Is Null) AND 
((i.strFileExclude)="n") AND 
((i.[strfilesignature] like F.strsignature)) AND 
((i.uidItemType)=1 Or 
(i.uidItemType)=5) AND 
((i.uidCollection)=@inputcolumn AND 
((i.strFileSignature) Not Like 'd0c%') AND 
((F.strFileProcessType) Not Like 'ZIP'));

提前致谢

更新:

所以我要进行以下操作。如果我取消注释 declare 和 select 子句并只是从 declare 向下执行，它会运行，如果我注释 declare 和 select 部分，它会在“;”附近显示错误。

UPDATE I  
SET 
    I.strFileProcesstype = F.STRFILEPROCESSTYPE, 
    I.strFileSignatureType = F.strfilesignaturetype
--declare @uidcollectionID int
--select I.strFileSignatureType
from 
tblItem I
    inner join tblFileSignature F
on 
I.strfilesignature = left(F.strsignature,len(I.strfilesignature))

WHERE I.strFileSignatureType Is Null 
    AND I.strFileExclude='n' 
    AND I.uidItemType in (1,5) 
    AND I.uidCollection = @uidCollectionID
    AND left(I.strFileSignature,3) <> 'd0c' 
    AND F.strFileProcessType <> 'ZIP';

有什么想法吗？

Answer 1

你应该改变

• 双引号到单引号
• * 到 %
• 用LIKE替换InStr

除此之外，我觉得还不错。