tsql : Access query to TSQL union update conversion correct?

Question

I have a query in access which i need to convert to a stored proc in sql server 2005.
the query in access is as follows:

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

thanks in advance

UPDATE:

so i'm going with the following. if i uncomment the declare and select clause and just execute from the declare down, it runs, if i comment the declare and select parts, it says 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

You should change the

• Double Quotes to Single Quotes
• * to %
• Replace the InStr with LIKE

Other than that, it looks fine to me.

Answer 2

No, you'd use a JOIN, not a UNION.

You can either make it a CROSS JOIN, and continue to apply the join conditions in the WHERE clause, or you can make it an inner join:

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

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

Answer 3

This should be close to what you need. May need to work on the join condition, but I think my conversion from INSTR will do it.

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