[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 510
  • Last Modified:

Inner join and update statement in Access


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
heyday2004
Asked:
heyday2004
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
GRayLCommented:
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

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