Inner Join with another table

[jmodo]

I am trying to replace records in one database with records in another database, where the records in both databases share a key.

DELETE * FROM [MainTable] AS mtMaster INNER JOIN [C:\Table2.mdb].MainTable AS mtImport ON mtMaster.troubleCall = mtImport.troubleCall

I am getting "Specify the table containing the records you want to delete." I have a feeling the "AS" statements aren't doing their job.
I hope someone can help me out. I haven't used SQL in quite a while, so I'm having trouble, especially with this second database, and the fact that the tables have the same names in each database.

Thanks!
J

 

[PHV]

You may try something like this:
DELETE mtMaster.*
FROM MainTable AS mtMaster INNER JOIN MainTable IN C:\Table2.mdb AS mtImport ON mtMaster.troubleCall = mtImport.troubleCall

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jmodo]

I am using this word for word, and I get a "Syntax error in FROM clause. Am I missing something?

Thanks!
J

 

[PHV]

And this ?
DELETE mtMaster.*FROM MainTable AS mtMaster INNER JOIN MainTable IN 'C:\Table2.mdb' AS mtImport ON mtMaster.troubleCall = mtImport.troubleCall

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

And this ?
DELETE mtMaster.*
FROM MainTable AS mtMaster INNER JOIN MainTable IN 'C:\Table2.mdb' AS mtImport ON mtMaster.troubleCall = mtImport.troubleCall

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jmodo]

No luck. I was using []'s around the path ,but i switched them to ''.

I am executing this within MS Access, if that helps.
Thanks,
J

 

[PHV]

BTW, why not simply use a linked table ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jmodo]

I'm just adding the answer here that I found. My problem has been solved:

DELETE FROM MainTable mtMaster WHERE EXISTS (SELECT * FROM MainTable mtImport IN 'C:\Table2.mdb' WHERE mtMaster.troubleCall = mtImport.troubleCall)

Access likes the subquery as opposed to the inner join.
Thanks for your help.