I can't find out if the below is possible. I want to run an update statement in one database from another database. I know that I can save a query and run that from the master database but I thought the below would be possible
UPDATE tblOne
INNER JOIN tblTwo
ON tblOne.[TID] = tblTwo.TID
SET tblTwo.PC = [tblOne].[PC]
from tblOne in 'C:\DB.mdb' ;
Any help would be greatly appreciated
Per the MSDN Documentation, the IN clause is only compatible when used in conjunction with the SELECT statement (i.e. Select queries), INSERT INTO statement (i.e. Append queries), or SELECT INTO statement (i.e. Make Table queries) - it cannot be used as part of an UPDATE statement.
I would suggest that you import tblOne as a linked table in the database in which you intend to execute your UPDATE query - this will also result in improved performance over the use of the IN clause.
To link the table, select Access Database from the Import panel of the External Data ribbon tab, and then select the Link option from the wizard:
After the table has been linked, your UPDATE statement will become simply:
UPDATE
tblOne INNER JOIN tblTwo ON tblOne.TID = tblTwo.TID
SET
tblTwo.PC = tblOne.PC
I think you just need to reverse what you’ve written because the table you’re updating needs to be the main table.
Try:
UPDATE tblTwo
INNER JOIN tblOne ON tblOne.[TID] = tblTwo.TID
SET tblTwo.PC = [tblOne].[PC] ;
Please note without a where clause you will be updating all rows.
