title: Access SQL Update One Table In Join Based on Value in Same Table
body: <p>I have a table in Access called "tempSpring_ASN", with the following fields (among others):</p>
  
  <p>SHP_CUSTOM_5 (AutoNumber)</p>
  
  <p>RECORD_TYPE (Text)</p>
  
  <p>PO_NUM (Text).</p>
  
  <p>I need to change the value of RECORD_TYPE, such that if PO_NUM is the same as PO_NUM in the previous record, then RECORD_TYPE should be "LIN", otherwise (or if it's the first record), RECORD_TYPE should be "HDR".</p>
  
  <p>I've created the following query to get the correct new value for RECORD_TYPE:</p>
  
  <pre><code>SELECT TOP 1 t1.SHP_CUSTOM_5,
      t1.PO_NUM AS CurrentValue,
      NULL AS PreviousValue,
      "HDR" AS RECORD_TYPE
  FROM tempSpring_ASN AS t1
  ORDER BY t1.SHP_CUSTOM_5
  UNION ALL
  SELECT t1.SHP_CUSTOM_5,
      t1.PO_NUM AS CurrentValue,
      t2.PO_NUM AS PreviousValue,
      IIf([CurrentValue]=[PreviousValue],'LIN','HDR') AS RECORD_TYPE
  FROM tempSpring_ASN AS t1,
      tempSpring_ASN AS t2
  WHERE t1.SHP_CUSTOM_5 = t2.SHP_CUSTOM_5 + 1
  ORDER BY t1.SHP_CUSTOM_5;
  </code></pre>
  
  <p>I've saved that query as "tempSpring_ASN_With_PreviousRow".  Now I'm trying to use that to update the original tempSpring_ASN table with the following query:</p>
  
  <pre><code>UPDATE tempSpring_ASN INNER JOIN tempSpring_ASN_With_PreviousRow ON tempSpring_ASN.SHP_CUSTOM_5 = tempSpring_ASN_With_PreviousRow.SHP_CUSTOM_5 SET tempSpring_ASN.RECORD_TYPE = [tempSpring_ASN_With_PreviousRow].[RECORD_TYPE];
  </code></pre>
  
  <p>but I'm getting, "Operation must use an updateable query."  I'm not sure if it's because I'm trying to update one table in a join, or because I'm trying to update a table based on a value in that same table, or due to something else.  Regardless, I'm looking for something which works.</p>
  
  <p>Thanks!</p>
  
  <p><strong>Update</strong> (no pun intended)<strong>:</strong></p>
  
  <p>I've tried the following update query:</p>
  
  <pre><code>UPDATE tempSpring_ASN INNER JOIN Table5 ON tempSpring_ASN.SHP_CUSTOM_5 = Table5.SHP_CUSTOM_5 SET tempSpring_ASN.RECORD_TYPE = "zzz";
  </code></pre>
  
  <p>and it worked fine.  The result was that tempSpring_ASN was updated, but Table5 wasn't.  Apparently, if two tables are joined in a SQL statement, even if you run an update on one table, it still won't try to update the other table in the join.  That being the case, I'm not sure why my original update query won't work.  I know that tempSpring_ASN_With_PreviousRow isn't updateable, because it's a UNION query, but I'm not <em>trying</em> to update it.  Rather I'm trying to update tempSpring_ASN--the other table in the join, which <em>is</em> updateable.</p>
  
  <p><strong>Update 2:</strong>
  Then I tried using a correlated subquery as follows:</p>
  
  <pre><code>UPDATE tempSpring_ASN AS t
  SET t.RECORD_TYPE = (
          SELECT RECORD_TYPE
          FROM (
              SELECT TOP 1 t1.SHP_CUSTOM_5,
                  t1.PO_NUM AS CurrentValue,
                  NULL AS PreviousValue,
                  "HDR" AS RECORD_TYPE
              FROM tempSpring_ASN AS t1
              ORDER BY t1.SHP_CUSTOM_5
              UNION ALL
              SELECT t1.SHP_CUSTOM_5,
                  t1.PO_NUM AS CurrentValue,
                  t2.PO_NUM AS PreviousValue,
                  IIf([CurrentValue] = [PreviousValue], 'LIN', 'HDR') AS RECORD_TYPE
              FROM tempSpring_ASN AS t1,
                  tempSpring_ASN AS t2
              WHERE t1.SHP_CUSTOM_5 = t2.SHP_CUSTOM_5 + 1
              ORDER BY t1.SHP_CUSTOM_5
              )
          WHERE SHP_CUSTOM_5 = t.SHP_CUSTOM_5
          );
  </code></pre>
  
  <p>but I'm still getting "Operation must use an updateable query." </p>
  
  <p><strong>Update 3:</strong></p>
  
  <p>I believe that the error is due to the fact that I'm using a union query.  To isolate the problem, I've tried the following (which would NOT give me the result I want, but would help me diagnose the problem.):</p>
  
  <pre><code>UPDATE tempSpring_ASN AS t
  SET t.RECORD_TYPE = (
          SELECT TOP 1 RECORD_TYPE
          FROM tempSpring_ASN_With_PreviousRow
          );
  </code></pre>
  
  <p>It gave me the same error.  So the question now becomes, why can't I use a single value of an output of a union query to set a value in a recordset?</p>