title: UPDATE with Pattern Matching (MS Access)
body: <h1>The Problem</h1>
  <p>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.</p>
  <h1>Example</h1>
  <p>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').</p>
  <p>So far, matching the items has not been a problem. The problem is setting <em>all</em> results equal to the proper value.</p>
  <p>As an example, the incoming data (in table 'incoming') looks like...</p>
  <pre><code>key    value
  P*     P-Type
  N*     N-Type
  </code></pre>
  <p>and the data to update (in table 'main') looks like...</p>
  <pre><code>name    type
  Py      &lt;Empty&gt;
  Pa      &lt;Empty&gt;
  Pi      &lt;Empty&gt;
  Ny      &lt;Empty&gt;
  Na      &lt;Empty&gt;
  Ni      &lt;Empty&gt;
  </code></pre>
  <p>I want to run an UPDATE query which should result in:</p>
  <pre><code>name    type
  Py      P-Type
  Pa      P-Type
  Pi      P-Type
  Ny      N-Type
  Na      N-Type
  Ni      N-Type
  </code></pre>
  <p>I tried a query like:</p>
  <pre><code>UPDATE incoming RIGHT JOIN main ON main.name LIKE incoming.key SET main.type=incoming.value;
  </code></pre>
  <p>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 <code>... SET main.type= (SELECT incoming.value FROM incoming WHERE main.name LIKE incoming.key)</code> does not yield the desired change. My efforts have only gotten me to:</p>
  <pre><code>name    type
  Py      P-Type
  Pa      &lt;Empty&gt;
  Pi      &lt;Empty&gt;
  Ny      N-Type
  Na      &lt;Empty&gt;
  Ni      &lt;Empty&gt;
  </code></pre>
  <p>How does Access get ALL the matching values to update?</p>
  <p>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?</p>