title: SQL Update woes in MS Access - Operation must use an updateable query
body: <p>I have a select query which does some text manipulation to essentially reformat a field so that I can look it up in another table:</p>
  
  <p>If my first table if I have a field like "J1/2" it looks up the ID of a record in a different table with J1 and J2 in the appropriate fields.</p>
  
  <p>This all works well.</p>
  
  <p>Now I want to update the original table so I don't have to do lookups using this string manipulation anymore, but my attempts at update queries end with "Operation must use an updateable query"</p>
  
  <p>Any ideas?</p>
  
  <p>My SELECT statement:</p>
  
  <pre><code>SELECT DISTINCT
  t1.DD,
  t1.TN,
  t1.DD &amp; " J" &amp; MID(t1.TN,2,1) AS CalculatedStart,
  t1.DD &amp; " J" &amp; MID(t1.TN,4,1) AS CalculatedEnd,
  t2.ID
  FROM t1 INNER JOIN t2
  ON (t1.DD &amp; " J" &amp; MID(t1.TN,2,1)=t2.StartLink)
  AND (t1.DD &amp; " J" &amp; MID(t1.TN,4,1)=t2.EndLink)
  WHERE t1.TN Like "J?/?"
  AND t1.DD Like "M*";
  </code></pre>
  
  <p>Recall - this works fine and I get the necessary t2.ID out the other end.</p>
  
  <p>So I want to do something like:</p>
  
  <pre><code>UPDATE t1 SET t2ID = (
      SELECT Query1.ID
      FROM Query1
      WHERE t1.DD=Query1.DD
      AND t1.TN=Query1.TN
      )
  WHERE t1.TN Like "J?/?"
  AND t1.DD Like "M*";
  </code></pre>
  
  <p>Only this fails.  This is within MS Access itself so I can't imagine an actual permissions problem like most of the "Operation must use an updateable query" problems seem to be.</p>
  
  <p>EDIT: Trying to simplify the case that doesn't work.</p>
  
  <p>This UPDATE query is fine:</p>
  
  <pre><code>UPDATE t1
  SET t2ID="Unknown"
  WHERE TN LIKE "J?/?"
  AND DD LIKE "M*";
  </code></pre>
  
  <p>This one fails (Thanks Goedke - this example obviously fails because the subquery returns more than 1 result.  I had oversimplified to try to find my problem)</p>
  
  <pre><code>UPDATE t1
  SET t2ID=(SELECT ID FROM t2)
  WHERE TN LIKE "J?/?"
  AND DD LIKE "M*";
  </code></pre>
  
  <p>So do I just have my subquery syntax wrong in some way?</p>
  
  <p>EDIT: This SELECT statement is fine too:</p>
  
  <pre><code>SELECT t1.OA, t1.DD, t1.TN, t1.HATRIS,
      query1.DD, query1.TN, query1.ID
  FROM t1 INNER JOIN query1
  ON t1.DD=query1.DD
  AND t1.TN=query1.TN
  </code></pre>
  
  <p>Furthermore, using count on the select statement above shows that there is exactly 1 ID being returned per (DD,TN) combination</p>
  
  <p>EDIT:</p>
  
  <p>The simplest case I've now got to - using various SELECT statements I now have a table with just 2 columns - the primary key of t1 and the value I want to insert into t1.</p>
  
  <p>I still can't seem to write</p>
  
  <pre><code>UPDATE t1 SET t1.f2 = (SELECT t2.f2 FROM t2 WHERE t2.f1 = t1.f1)
  </code></pre>
  
  <p>where t1's primary key is f1.  Even adding WHERE t1.f1 IN (SELECT f1 FROM t2) doesn't help. (Added to eliminate the possibility that the subquery returns 0 results)</p>