ACCESS/SQL：如何同时插入/更新？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4667743/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ACCESS/SQL: How to INSERT INTO/UPDATE at the same time?
提问by Christian M
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.
这是我的情况。首先，我不是在研究关系数据库，我只是将访问用作操作数据的简单方法。目前，我有很多桌子。
一个主表，我们称之为 MAIN，还有大约 10 个其他表，我们可以称之为 X1、X2、X3 等。X1 表包含具有 X1 属性的项目。X2 表包含具有 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 等，它们是布尔值。
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.
我想用 Xx 表中的数据为 MAIN 表提供数据。
事情是，可以有具有多个属性的项目，因此它们可以出现在例如 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.
但它什么也没给。现在我想这只是合乎逻辑的，因为 MAIN 表中还没有任何记录。
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.
无论如何，我可以编写什么查询来执行此操作：
如果 MAIN 中尚不存在表 X1 的记录，则添加它并将 X1 字段设置为 true。
如果 MAIN 中已经存在 X1 的记录，则更新它并将 X1 字段设置为 true。
(Then I would update it to run on every X table I have.)
（然后我会更新它以在我拥有的每个 X 表上运行。）
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 >_>)
我正在考虑 INSERT INTO，但我不想覆盖已经存在的数据或产生错误（我真的不太了解这一切 >>>）
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)
编辑 1
我以为我会首先尝试将 Xx 表中的所有数据插入到 MAIN 表中（它们具有相同的结构）
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.
我想一旦我添加了 X 表中的所有数据，然后我对每个 Xx 表运行一些带有 WHERE EXISTS 的 UPDATE，将相应的 Xx 属性设置为 true，然后我就完成了。
But I'm having a hard time even doing something as "simple" as merging the data from a few tables into one ....
但是我什至很难做一些“简单”的事情，比如将几个表中的数据合并成一个......
采纳答案by Christian M
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.
对每个 Xx 表重复。处理重复项。
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.
对每个 Xx 表重复。
propably not the most efficient way (had to edit the queries 24 times actually...)
but hey, it worked...
可能不是最有效的方式（实际上必须编辑查询 24 次......）
但是，嘿，它有效......
Now, if someone has a way to do this in one shot...
现在，如果有人有办法一次性做到这一点......
回答by RolandTumble
First off, you areworking with a relational databse, even if it's a badly designed one.
首先，你是有关系DATABSE工作，即使它是一个设计糟糕的一个。
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.
其次，您将无法使用 SQL 在同一语句中同时插入和更新。SQL Server 2008 中引入了一个特殊的子句，但它既不是标准 SQL 也不是 Access 变体的一部分。
Third, you're almostright with your INSERTstatement in your edit 1, but not quite. Try this instead:
第三，您在编辑 1 中的陈述几乎是正确的，但并不完全正确。试试这个：INSERT
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, field1and field2are stand-ins for allof 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 SELECTthat participates in the UNIONhave 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 xXfield.
在代码中，field1并且field2是替身所有的“同场”，所有的表都具有共同的，和列表x1，x2（下略），需要继续下去，直到你已经得到了所有的XX领域中主要包括在内。然后，在每一个SELECT，在参加UNION尽可能多False的，因为它需要以匹配主要的场数，减去真实位置上被占领的一个True（注：不包括引号，如果它是一个真正的布尔数据类型-它不能Access 中为空）xX字段。
This assumes, of course, that I've correctly understood your poorly-described schema....
当然，这假设我已经正确理解了您描述不佳的架构....
回答by Andrew
if EXISTS(*SELECT STATEMENT*)
Begin
//Update
end
ELSE
BEGIN
//Insert
END
