Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Inner Join with another table

Status
Not open for further replies.

jmodo

Programmer
Aug 2, 2006
43
US
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
 
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
 
I am using this word for word, and I get a "Syntax error in FROM clause. Am I missing something?

Thanks!
J
 
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
 
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
 
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
 
BTW, why not simply use a linked table ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top