title: SQL - copy multiple column values from one row to another with exceptions
body: <p>I have an Access-compatible database (JET engine, .mdb file) which I manipulate via VBA in Excel. Queries and updates are written in SQL by VBA.</p>
  
  <p>I need to copy across values from one row to another - according to certain conditions. Let me give you an example:</p>
  
  <pre><code>ID  Del     Cat User    Col1    Col1    Col3    Col4    ... Col40
  1   False   M   A       xyz     NULL    NULL    xyz     ... xyz
  2   True    M   B       NULL    NULL    NULL    NULL    ... xyz
  3   False   C   C       NULL    xyz     NULL    NULL    ... xyz
  4   False   C   D       xyz     NULL    NULL    xyz     ... xyz
  5   False   C   E       NULL    NULL    NULL    NULL    ... NULL
  6   True    C   F       xyz     xyz     NULL    NULL    ... xyz
  7   False   S   G       xyz     xyz     NULL    xyz     ... NULL
  8   False   S   H       NULL    NULL    xyz     NULL    ... NULL
  9   False   S   F       NULL    NULL    NULL    NULL    ... NULL
  10  False   S   J       xyz     NULL    NULL    xyz     ... NULL
  </code></pre>
  
  <p>ID is an autoincrement primary key.
  Del is a boolean indicating whether the record has been marked as deleted (soft-delete)
  Cat is the category of the record. 3 categories exist: M, C, S.</p>
  
  <p>Let's look at row IDs 6 &amp; 9. They both relate to user "F", but they are in different categories (C and S). However the row in Cat C has been flagged as deleted (Del=True), while S is not deleted (Del=False).</p>
  
  <p>What I want to do is update row 9 (the non-deleted record for user "F"), so that the values of all the substantive data columns (Col1.. Col40) are copied across from row 6. I'd like row 9 to end up looking like this:</p>
  
  <pre><code>9   False   S   F   xyz     xyz NULL    NULL    ... xyz
  </code></pre>
  
  <p>i.e: identical to row 6 except for the ID; the Del flag (which should remain True for row 6 but False for row 9); and Cat column (which should remain C for row 6 but S for row 9).</p>
  
  <p>I'm aware that other similar questions exist:</p>
  
  <ul>
  <li><a href="https://stackoverflow.com/questions/149784/how-do-you-copy-a-record-in-a-sql-table-but-swap-out-the-unique-id-of-the-new-ro">How do you copy a record in a SQL table but swap out the unique id of the new row?</a></li>
  <li><a href="https://stackoverflow.com/questions/3770012/copy-rows-from-the-same-table-and-update-the-id-column/23497078">Copy rows from the same table and update the ID column</a></li>
  </ul>
  
  <p>... however it's not clear to me exactly how do apply this to what I want, with the exclusion of copying ID, Del and Cat. Additionally, in the examples given in other questions, there are only a handful of columns - but I have over 40. I'm not sure if there's a "blanket" way to copy across all columns with the exclusion of just a couple, or if I have to write all of them out individually (except the ones I don't want copied).</p>
  
  <p>Basically, I know it's going to be something like:</p>
  
  <blockquote>
    <p>UPDATE [TABLENAME] SET (however to choose all fields except ID, Del
    and Cat) WHERE User="F";</p>
  </blockquote>
  
  <p>... however as this is just an example, using User "F", I don't want to have to specify an individual user in hard code. I want this to apply to all users, no matter what the name. Basically, the logic is:</p>
  
  <p>"Look at all rows in the C category with a Deleted=True flag. Then for any deleted C rows, check whether the same User names exist within any other category (whether S or M). For any found, copy the data from the rest of the columns from the deleted record to the non-deleted records - although do not copy the category, deleted or ID values".</p>
  
  <p>I hope this is clear. Grateful for any help from you seasoned SQL people out there!</p>