MS Access 在不存在的地方插入

本文介绍了MS Access 在不存在的地方插入的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有下表:

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