Update one table from another table in MS Access

Can any one tell me that in MS Access 2000 Queries
like SQL Server Update .. From clause works on not.

I want to use "UPDATE tablename1 FROM tablename2 SET ..."


I mean I have to Update some fields of table A with table B fields on some Key.


Pl. let ke know ASAP.

Thanks

hnd_raAsked:
Who is Participating?
 
Arthur_WoodConnect With a Mentor Commented:
the Query Builder in Access 2000 will genrate the UPDATE something like this:

UPDATE Table1 INNER JOIN Table1 ON Table1.OrderID = Table2.OrderID SET Table1.CustomerID = [Table2].[CustomerID], Table1.EmployeeID = [Table2].[CustomerID], Table1.OrderDate = [Table2].[OrderDate];

Arthur Wood
0
 
iboutchkineCommented:
For MS Access
=============
INSERT INTO Table1 IN 'ExternalDatabasePath1' SELECT *
FROM Table1 IN 'ExternalDatabasePath2';
where externaldatabasepath1 & 2 are something like c:\databases\db1.mdb & c:\databases\db2.mdb
(INSERT INTO Table1 SELECT * FROM Table2  IN 'c:/aaa/bbb/Database.mdb')
0
 
hnd_raAuthor Commented:
Dear iboutchkine ,
       Thanks, But I have to update not insertion, And Table1 and Table2 r having different structure.

0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Arthur_WoodCommented:
what you are trying to do sounds VERY suspect, as it is USUALLY not a good idea to have the SAME data in two different tables , unless there is VERY good reason to have this duplication.  Why do you not simply create query to JOIN the two tables, and then retrive the data from Table2.

Also, if the two tables have DIFFERENT STRUCTURES, then the question becomes which fields from table 1 are to be updated from which fields in Table2.  You will need to provide a lot more descriptive information about the two tables, before anyone can help you.

I doubt that the Access query engine is smart enough to KNOW which fields in the two tables are the same (name is NOT always enough).

Arthur Wood
0
 
Arthur_WoodCommented:
as an alternative, go into the Access query Builder, build the necessary UPDATE query, and then look at the SQL that the Query Builder will generate to carry out the necessary Update.

Arthur Wood
0
 
Arthur_WoodCommented:
the beginning should read:

UPDATE Table1 INNER JOIN Table2 ON Table1. ...
0
 
Éric MoreauSenior .Net ConsultantCommented:
the syntax is (at least with SQL Server):

Update AliasA
Set Field1 = AliasB.Field
FROM Table1 AS AliasA
inner join Table2 AS AliasB
ON AliasA.FieldKey = AliasB.FieldKey
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.