Incorrect syntax near keyword 'INNER' 2

[rookery]

Can someone tell me what is wrong with the following query which I'm running from Access connected to SQL B/E:

UPDATE [Phone Users] INNER JOIN tblTempMismatches ON [Phone Users].EmployeeID = tblTempMismatches.EmployeeID SET [Phone Users].CostCentre = [RemoteCostCentre]
WHERE (((tblTempMismatches.ImportLogNumber )= 83) AND ((tblTempMismatches.StatusTypeDecode)=2))

I'm using ADO execute statement which has worked fine before and I'm getting error:

"Incorrect syntax near keyword 'INNER'"

Any ideas?

 

[mimi2]

try this

UPDATE Phone_Users
set
[Phone Users].CostCentre = [RemoteCostCentre]
from
[Phone Users] INNER JOIN tblTempMismatches
ON [Phone Users].EmployeeID = tblTempMismatches.EmployeeID
WHERE (((tblTempMismatches.ImportLogNumber )= 83) AND ((tblTempMismatches.StatusTypeDecode)=2))

 

[rookery]

Thanks for your reply but before I try it Mimi2 can I just add that if I paste it into SQL View it works fine.

Would this be expected?

 

[rookery]

It doesnt work. Any other ideas?

 

[MKVAB]

looks like you've got your FROM an SET swapped.

Try this:

UPDATE [Phone Users] 
SET [Phone Users].CostCentre = [RemoteCostCentre] 
FROM [Phone Users] INNER JOIN tblTempMismatches 
  ON [Phone Users].EmployeeID = tblTempMismatches.EmployeeID
WHERE (((tblTempMismatches.ImportLogNumber )= 83)
AND ((tblTempMismatches.StatusTypeDecode)=2))

-MK

 

[rookery]

Thnaks guys for your posts. Your syntax is correct. Apologies to Mimi for saying different first time round.

However originally the app was hanging on the correct syntax but I worked out that this was because a Select Query on the table to be updated was open. Closed it down and hay presto!

Cheers.

 

[SQLBill]

AAHHHH! Mimi2's would have worked if anyone had caught the error in the first line....

UPDATE Phone_Users

Notice the 'underscore' in the table name. Even I didn't catch it until I compared MKVAB's to Mimi2's.

-SQLBill

 

[rookery]

Re-visiting this thread, would I be correct in saying that the FROM and SET statements should be swapped round depending on whether you're using it in Access or SQL?

This seems to be the case. Can anyone elaborate?

 

[rookery]

By way of follow up just read that Access SQL doesn't support any sub-statements in the SET clause whereas SQL 2000 does.

If you want to do it in Access you have to include it in the UPDATE clause which explains the difference.