Tsql: Is the TSQL connection update conversion access request correct?

Question

I have an access request that I need to convert to a saved proc in sql server 2005. The
access request looks like this:

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"));

in tsql .. would it be the same?

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'));

early

UPDATE:

so I'm going to do the following. if I uncomment the declare and select declaration and just execute it from the declare down declaration, it starts, if I comment on the declaration and select the details, it says an error near ';'.

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';

any ideas?

Answer 1

, . , , INSTR .

UPDATE i
    SET strFileProcesstype = fs.STRFILEPROCESSTYPE, 
        strFileSignatureType = fs.strfilesignaturetype
    FROM tblitem i
        INNER JOIN tblFileSignature fs
            ON i.strfilesignature = LEFT(fs.strsignature, LEN(i.strfilesignature))
    WHERE i.strFileSignatureType IS Null 
        AND i.strFileExclude='n' 
        AND i.uidItemType IN (1,5)
        AND i.uidCollection = @inputcolumn 
        AND LEFT(i.strFileSignature,3) <> 'd0c' 
        AND fs.strFileProcessType <> 'ZIP';

Answer 2

• Double Quotes to Single Quotes
• * to %
• InStr LIKE

, .

Answer 3

No, you should use JOIN, not UNION.

You can do this CROSS JOIN and continue to apply join conditions in the WHERE clause, or you can do this inner join:

from tblItem as I INNER JOIN tblFileSignature as F 
ON ((InStr(i.[strfilesignature],F.[strsignature]))=1)

And remove this condition from the WHERE clause (also Lieven's answer).