ACCESS/SQL:如何同时插入INTO/UPDATE? [英] ACCESS/SQL: How to INSERT INTO/UPDATE at the same time?
问题描述
这是我的情况.
首先，我不在关系数据库上工作，我只是将访问作为一种简单的方式来处理数据.
目前，我有很多桌子.
一个主表，我们称其为MAIN，另外约10个表，我们可以称其为X1，X2，X3等.
X1表包含具有X1属性的项目. X2表包含具有X2属性的项目，依此类推.
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.
所有Xx表都具有相同的字段. MAIN表也具有相同的字段，另外还有布尔值X1，X2等.
All Xx tables have the same fields. MAIN table has the same fields too, with, in addition, fields X1, X2, etc. which are Boolean.
我想做什么:
我想用来自Xx表的数据来提供MAIN表.
事实是，可能存在具有多个属性的项目，因此它们可以出现在例如X1，X2，X5中.
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";
但是它没有任何作用.现在，我认为这只是合乎逻辑的，因为MAIN表中还没有任何记录.
but it doesn't give anything. now I suppose this is only logical since there aren't any records yet in the MAIN table.
无论如何，我可以编写什么样的查询来做到这一点:
如果表X1的记录在MAIN中尚不存在，请将其添加并将X1字段设置为true.
如果MAIN中已经存在X1的记录，请对其进行更新并将X1字段设置为true.
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.
(然后，我将对其进行更新以在我拥有的每个X表上运行.)
(Then I would update it to run on every X table I have.)
我正在考虑将INSERT INTO，但是我不想覆盖已经存在的数据或产生错误(我对这一切都不太了解> _>)
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.
修改1
我以为我会先尝试将Xx表中的所有数据插入到MAIN表中(它们具有相同的结构)
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 :/
我想一想我已经添加了X表中的所有数据，然后为每个Xx表运行了带有WHERE EXISTS的UPDATE，并将相应的Xx属性设置为true，我就完成了.
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 ....
推荐答案
这是我在此期间使用的替代解决方案:
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);
对每个Xx表重复
.会处理重复项.
repeated for each Xx table. takes care of duplicates.
然后，添加属性:
UPDATE MAIN SET X1 = true
WHERE exists (select *
from X1
where X1.itemnumber = MAIN.itemnumber);
针对每个Xx表重复
不是最有效的方法(实际上必须将查询编辑24次...)
但嘿，它奏效了...
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...
这篇关于ACCESS/SQL:如何同时插入INTO/UPDATE?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
