• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 148
  • Last Modified:

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

0
hnd_ra
Asked:
hnd_ra
1 Solution
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 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
 
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now