The Problem
Running an UPDATE query with pattern matching is not updating all the values on the desired table. This is using Office 365 with the defaults for Access, so no split tables or SQL backend involved here.
Example
Some incoming data has a bunch of key-value pairs where the key is a pattern to match. The overall approach is to import the data into a table (‘incoming’) and execute and UPDATE query with LIKE to update the values on the main table (‘main’).
So far, matching the items has not been a problem. The problem is setting all results equal to the proper value.
As an example, the incoming data (in table ‘incoming’) looks like…
key value P* P-Type N* N-Type
and the data to update (in table ‘main’) looks like…
name type Py <Empty> Pa <Empty> Pi <Empty> Ny <Empty> Na <Empty> Ni <Empty>
I want to run an UPDATE query which should result in:
name type Py P-Type Pa P-Type Pi P-Type Ny N-Type Na N-Type Ni N-Type
I tried a query like:
UPDATE incoming RIGHT JOIN main ON main.name LIKE incoming.key SET main.type=incoming.value;
but this only updates the FIRST match. Fooling with right/left joins doesn’t seem to do the trick. Attempting to set values to a subquery such as ... SET main.type= (SELECT incoming.value FROM incoming WHERE main.name LIKE incoming.key) does not yield the desired change. My efforts have only gotten me to:
name type Py P-Type Pa <Empty> Pi <Empty> Ny N-Type Na <Empty> Ni <Empty>
How does Access get ALL the matching values to update?
As a note: this query is being called by VBA, so I could brute-force iterate the record set. This is undesirable, as it is more complicated and (I assume) slower compared to an SQL query. Can access do this with an SQL query and what is that general pattern?
Advertisement
Answer
Turns out that you cannot rely on the ‘View: Datasheet’ option when performing an UPDATE query like this. The ‘why’ of this remains a mystery to present me.
This kind of query actually works just fine when you hit RUN. Verify it on the ‘main’ table.