?
Solved

Inner join and update statement in Access

Posted on 2009-05-11
2
Medium Priority
?
512 Views
Last Modified: 2012-05-06

I saw below SQL statement in an Access project:
  db.Execute "UPDATE tableA INNER JOIN tableB" _
               & "ON tableA.NUMBER = tableB.Number_ASC" _
               & "Set tableA.field1 = tableB.field1
            ...

I don't have much experience in Access, could anyone explain this query? Is it a common practice in Access? BTW, I know inner join, but am just not used to the usage of the update statement here. Thanks.

 
0
Comment
Question by:heyday2004
2 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1600 total points
ID: 24352281
that UPDATE does a update of tableA, if there are matching rows in tableB, based on the join, and assigns the value of field1 of tableb for the matching row to tableA.field1.

and yes, this is common practice in access.
0
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 400 total points
ID: 24354953
It will only be an updatable query if the fields in the ON clause are primary keys (no duplicates).  If there was ever an instance where there were two or more records from tableB matching the tableA join, the update would fail.
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Implementing simple internal controls in the Microsoft Access application.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

592 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