Solved

Inner join and update statement in Access

Posted on 2009-05-11
2
501 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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 400 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 100 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

810 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