title: ACCESS/SQL: How to INSERT INTO/UPDATE at the same time?
body: <p>Here is my situation.
  1st of all, I'm not working on a relational database, I'm merely using access as an easy way to manipulate data.
  At the moment, I have a lot of tables.<br>
  One main table, let's call it MAIN, and about 10 other tables, we can call X1, X2, X3, etc.
  X1 table contains items that have the X1 property. X2 table contains items that have the X2 property, and so on.  </p>
  
  <p>All Xx tables have the same fields. MAIN table has the same fields too, with, in addition, fields X1, X2, etc. which are Boolean.</p>
  
  <p><strong>What I want to do:</strong></p>
  
  <p>I want to feed the MAIN table with data from the Xx tables.<br>
  thing is, there can be items that have multiple properties, so they can appear for example in X1, X2, X5.  </p>
  
  <p>So I tried at first running this:  </p>
  
  <pre><code>UPDATE MAIN
  SET itemnumber = X1.itemnumber, x1 = "true";
  </code></pre>
  
  <p>but it doesn't give anything. now I suppose this is only logical since there aren't any records yet in the MAIN table.  </p>
  
  <p>Anyway, what query can I write that will do this:<br>
  If the record of table X1 does not exist in MAIN yet, add it and set the X1 field to true.<br>
  If the record of X1 already exists in MAIN, update it and set the X1 field to true.</p>
  
  <p>(Then I would update it to run on every X table I have.)</p>
  
  <p>I'm considering INSERT INTO, but I don't want to overwrite data that already exists or generate an error (I really don't know much about all this >_>)</p>
  
  <p>Thanks in advance to whoever can provide tips.</p>
  
  <p><strong>edit 1</strong><br>
  I thought that I would first try to insert all the data from the Xx tables into the MAIN table (they have the same structure)  </p>
  
  <p>so I tried this at first:  </p>
  
  <pre><code>INSERT INTO MAIN.itemnumber
  (select X1.itemnumber from X1
  UNION ALL
  select X2.itemnumber from X2)
  </code></pre>
  
  <p>tried it on just one field to see if it works, but it doesn't :/  </p>
  
  <p>I figured once I've added all the data from the X tables, then I run a few UPDATE with a WHERE EXISTS for each Xx table, setting the according Xx property to true, and I'm done.  </p>
  
  <p>But I'm having a hard time even doing something as "simple" as merging the data from a few tables into one ....</p>