ACCESS to MSSQL Update Query

[jozsefusa]

I run the following statement from an update query in access but I can't find the way to run this same query in MSSQL. Please give me some ideas how to modify and run this in MSSQL.

Thank you

"UPDATE DISTINCTROW ZipToTerr, leadsUS SET leadsUS.Terr = [ZipToTerr]![TerrNum] WHERE ((([ZipToTerr].[BU]='W') AND (([ZipToTerr].[ZipFrom])<=[zip]) And (([ZipToTerr].[ZipTo])>=[zip])) And (([leadsUS].[terr]) = 1 ));"

[rmiao]

Tried this?

UPDATE ZipToTerr, leadsUS SET leadsUS.Terr = [ZipToTerr].[TerrNum] WHERE ((([ZipToTerr].[BU]='W') AND (([ZipToTerr].[ZipFrom])<=[zip]) And (([ZipToTerr].[ZipTo])>=[zip])) And (([leadsUS].[terr]) = 1 ))

[jozsefusa]

no didn't work

[rmiao]

Any error message? Where is the table?

[skhanal]

In SQL Server you can only have one table in UPDATE statement. If you need to join them to update a table, then you have to use FROM clause.

[jozsefusa]

Thanks for the help.
I have never used the FROM and UPDATE together. Would you mind to give me some hints on the syntax.
Thank you

UPDATE leadsUS
FROM ZipToTerr
SET leadsUS.Terr = [ZipToTerr].[TerrNum]
WHERE ((([ZipToTerr].[BU] = 'W') AND (([ZipToTerr].[ZipFrom]) <= [zip]) AND (([ZipToTerr].[ZipTo]) >= [zip])) AND (([leadsUS].[terr]) = 1))

[rmiao]

Should be:

UPDATE leadsUS
SET leadsUS.Terr = [ZipToTerr].[TerrNum] FROM ZipToTerr
WHERE ((([ZipToTerr].[BU] = 'W') AND (([ZipToTerr].[ZipFrom]) <= [zip]) AND (([ZipToTerr].[ZipTo]) >= [zip])) AND (([leadsUS].[terr]) = 1))

[jozsefusa]

You are the best!
It works, thanks you all for the help!

[rmiao]

You can find t-sql syntax and lof of sample codes in sql books online.