MS Access 在不存在的地方插入 [英] MS Access Insert where not exists
问题描述
我有下表:
+-----------+--------+
| FirstName | Active |
+-----------+--------+
| Rob | TRUE |
| Jason | TRUE |
| Mike | FALSE |
+-----------+--------+
只有当 John 的条目不存在时,我才想插入John"(Active=True)其中 Active=True.
我尝试以下操作:
I would like to insert 'John' (with Active=True) only if an entry for John doesn't exist already where Active=True.
I try the following:
insert into testTable (FirstName, Active) values ('John',True) where not exists (select 1 from testTable where FirstName='John' and Active=True)
但我明白了'查询输入必须至少包含一个表或查询'.
but i get 'Query input must contain at least one table or query'.
任何人都可以帮助我实现目标吗?
Can anybody help with what I am trying to achieve?
推荐答案
您不能将 Values
与 WHERE
子句结合使用.您需要改用 INSERT INTO ... SELECT
.
You can't combine Values
with a WHERE
clause. You need to use INSERT INTO ... SELECT
instead.
由于您不想从表中插入值,因此您需要使用虚拟表.我为此使用 MSysObjects
(这是一个始终存在且始终包含行的系统表):
Since you don't want to insert values from a table, you need to use a dummy table. I use MSysObjects
for that purpose (that's a system table that always exists and always contains rows):
INSERT INTO testTable (FirstName, Active)
SELECT 'John', True
FROM (SELECT First(ID) From MSysObjects) dummy
WHERE NOT EXISTS (select 1 from testTable where FirstName='John' and Active=True)
这篇关于MS Access 在不存在的地方插入的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!