ACCESS/SQL: How to INSERT INTO/UPDATE at the same time?

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.

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.

All Xx tables have the same fields. MAIN table has the same fields too, with, in addition, fields X1, X2, etc. which are Boolean.

What I want to do:

I want to feed the MAIN table with data from the Xx tables.

开发者_运维问答 thing is, there can be items that have multiple properties, so they can appear for example in X1, X2, X5.

So I tried at first running this:

UPDATE MAIN
SET itemnumber = X1.itemnumber, x1 = "true";

but it doesn't give anything. now I suppose this is only logical since there aren't any records yet in the MAIN table.

Anyway, what query can I write that will do this:

If the record of table X1 does not exist in MAIN yet, add it and set the X1 field to true.

If the record of X1 already exists in MAIN, update it and set the X1 field to true.

(Then I would update it to run on every X table I have.)

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 >_>)

Thanks in advance to whoever can provide tips.

edit 1

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)

so I tried this at first:

INSERT INTO MAIN.itemnumber
(select X1.itemnumber from X1
UNION ALL
select X2.itemnumber from X2)

tried it on just one field to see if it works, but it doesn't :/

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.

But I'm having a hard time even doing something as "simple" as merging the data from a few tables into one ....

---

First off, you are working with a relational databse, even if it's a badly designed one.

Second, you won't be able to use SQL to both insert and update in the same statement. A special clause for that was introduced in SQL Server 2008, but it's not part of either standard SQL nor the Access variant.

Third, you're almost right with your INSERT statement in your edit 1, but not quite. Try this instead:

INSERT INTO MAIN (
  field1,
  field2,
  x1,
  x2,
  ...)
SELECT
  field1,
  field2,
  True,
  False,
  ...
FROM X1
UNION ALL
SELECT
  field1,
  field2,
  False,
  True,
  ...
FROM X2
...

In the code, field1 and field2 are stand-ins for all of the "same fields" that all the tables have in common, and the list of x1, x2, &c., needs to continue until you've got all the xX fields in MAIN included. Then in each SELECT that participates in the UNION have as many False's as it takes to match the field count in MAIN, minus the one that's positionally occupied by the True (NB: no quotes if it's a real Boolean data type--which can't be Null in Access) for the xX field.

This assumes, of course, that I've correctly understood your poorly-described schema....

---

Here is the alternative solution I used in the meantime:

INSERT INTO MAIN
SELECT X1.itemnumber AS itemnumber
FROM X1
WHERE not exists (select itemnumber
from MAIN
where MAIN.itemnumber = X1.itemnumber);

repeated for each Xx table. takes care of duplicates.

then, to add the property:

UPDATE MAIN SET X1 = true
WHERE exists (select *
from X1
where X1.itemnumber = MAIN.itemnumber);

repeated for each Xx table.

propably not the most efficient way (had to edit the queries 24 times actually...)
but hey, it worked...

Now, if someone has a way to do this in one shot...

---

if EXISTS(*SELECT STATEMENT*)
Begin 
    //Update
end
ELSE
BEGIN 
   //Insert
END