Solved

MS Access 2010 Query to update one table from another

Posted on 2015-02-04
5
209 Views
Last Modified: 2015-02-04
Hi ,

     I was looking for the query to update a few colums (col1,col2,col3)  in TableA   from an identical table TableB  ( where the condition TableA.PrimKey = TableB.PrimKey)

   Can you please help
0
Comment
Question by:Sam OZ
5 Comments
 
LVL 18

Assisted Solution

by:SimonAdept
SimonAdept earned 75 total points
Comment Utility
update tableA
set A.col1=B.col1, A.col2=B.col2
from tableA A inner join tableB B on A.Primkey=B.Primkey
0
 

Author Comment

by:Sam OZ
Comment Utility
Thanks Simon . But is this tested in MS Access  ? I am getting an error   "Syntax error  Missing operator
 in query expression  from tableA  as  A inner join tableB as B on A.Primkey=B.Primkey
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 350 total points
Comment Utility
update tableA
inner join tableB On A.Primkey=B.Primkey
set A.col1=B.col1, A.col2=B.col2
0
 
LVL 18

Expert Comment

by:SimonAdept
Comment Utility
Sorry, not tested on Access, and written with the wrong flavour of SQL in mind. Apologies
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 75 total points
Comment Utility
When you don't know the correct syntax, try using the QBE to build the query.  In this case:
1. Add both tables to the grid.
2. Draw a join line to connect the two tables on the appropriate columns.
3. Select the columns from the table you want to update
4. Change the query type to Update - Access adds a new row to the grid.
5. in the update To row, type the tablename.columnname of the source data.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now