MS Access Insert where not exists
up vote
2
down vote
favorite
I have the following table:
+-----------+--------+
| FirstName | Active |
+-----------+--------+
| Rob | TRUE |
| Jason | TRUE |
| Mike | FALSE |
+-----------+--------+
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?
ms-access insert conditional where exists
edited Sep 13 at 9:30
krish KM
4,1221727
asked Sep 13 at 8:47
userod
112
add a comment |
up vote
2
down vote
favorite
I have the following table:
+-----------+--------+
| FirstName | Active |
+-----------+--------+
| Rob | TRUE |
| Jason | TRUE |
| Mike | FALSE |
+-----------+--------+
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?
ms-access insert conditional where exists
edited Sep 13 at 9:30
krish KM
4,1221727
asked Sep 13 at 8:47
userod
112
I'm sorry about the format of the table. I am still not sure how to paste a table onto here (any help there would also be appreciated..)
– userod
Sep 13 at 8:54
add a comment |
up vote
2
down vote
favorite
up vote
2
down vote
favorite
I have the following table:
+-----------+--------+
| FirstName | Active |
+-----------+--------+
| Rob | TRUE |
| Jason | TRUE |
| Mike | FALSE |
+-----------+--------+
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?
ms-access insert conditional where exists
edited Sep 13 at 9:30
krish KM
4,1221727
asked Sep 13 at 8:47
userod
112
I have the following table:
+-----------+--------+
| FirstName | Active |
+-----------+--------+
| Rob | TRUE |
| Jason | TRUE |
| Mike | FALSE |
+-----------+--------+
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?
ms-access insert conditional where exists
ms-access insert conditional where exists
edited Sep 13 at 9:30
krish KM
4,1221727
asked Sep 13 at 8:47
userod
112
edited Sep 13 at 9:30
krish KM
4,1221727
asked Sep 13 at 8:47
userod
112
edited Sep 13 at 9:30
krish KM
4,1221727
edited Sep 13 at 9:30
krish KM
4,1221727
edited Sep 13 at 9:30
krish KM
4,1221727
4,1221727
asked Sep 13 at 8:47
userod
112
asked Sep 13 at 8:47
userod
112
asked Sep 13 at 8:47
userod
112
112
I'm sorry about the format of the table. I am still not sure how to paste a table onto here (any help there would also be appreciated..)
– userod
Sep 13 at 8:54
add a comment |
I'm sorry about the format of the table. I am still not sure how to paste a table onto here (any help there would also be appreciated..)
– userod
Sep 13 at 8:54
I'm sorry about the format of the table. I am still not sure how to paste a table onto here (any help there would also be appreciated..)
– userod
Sep 13 at 8:54
I'm sorry about the format of the table. I am still not sure how to paste a table onto here (any help there would also be appreciated..)
– userod
Sep 13 at 8:54
add a comment |
1 Answer
1
active
oldest
votes
up vote
1
down vote
You can't combine Values with a WHERE clause. You need to use INSERT INTO ... SELECT instead.
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)
answered Sep 13 at 9:05
Erik von Asmuth
18.3k62038
1
Thanks, this works if I am executing directly in Access, but given I am generating the input query via a VB script and executing it from there with and ("ADODB.Connection") (Provider=Microsoft.ACE.OLEDB.12.0), I get an 'Record(s) cannot be read; no read permission on 'MSysObjects'. Any other ideas? thanks a lot
– userod
Sep 13 at 9:32
Well, usetestTableinstead ofMSysObjectsas your dummy table if you're sure that always contains records, or create your own dummy table.
– Erik von Asmuth
Sep 13 at 9:35
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f52309940%2fms-access-insert-where-not-exists%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
You can't combine Values with a WHERE clause. You need to use INSERT INTO ... SELECT instead.
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)
answered Sep 13 at 9:05
Erik von Asmuth
18.3k62038
1
Thanks, this works if I am executing directly in Access, but given I am generating the input query via a VB script and executing it from there with and ("ADODB.Connection") (Provider=Microsoft.ACE.OLEDB.12.0), I get an 'Record(s) cannot be read; no read permission on 'MSysObjects'. Any other ideas? thanks a lot
– userod
Sep 13 at 9:32
Well, usetestTableinstead ofMSysObjectsas your dummy table if you're sure that always contains records, or create your own dummy table.
– Erik von Asmuth
Sep 13 at 9:35
add a comment |
up vote
1
down vote
You can't combine Values with a WHERE clause. You need to use INSERT INTO ... SELECT instead.
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)
answered Sep 13 at 9:05
Erik von Asmuth
18.3k62038
1
Thanks, this works if I am executing directly in Access, but given I am generating the input query via a VB script and executing it from there with and ("ADODB.Connection") (Provider=Microsoft.ACE.OLEDB.12.0), I get an 'Record(s) cannot be read; no read permission on 'MSysObjects'. Any other ideas? thanks a lot
– userod
Sep 13 at 9:32
Well, usetestTableinstead ofMSysObjectsas your dummy table if you're sure that always contains records, or create your own dummy table.
– Erik von Asmuth
Sep 13 at 9:35
add a comment |
up vote
1
down vote
up vote
1
down vote
You can't combine Values with a WHERE clause. You need to use INSERT INTO ... SELECT instead.
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)
answered Sep 13 at 9:05
Erik von Asmuth
18.3k62038
You can't combine Values with a WHERE clause. You need to use INSERT INTO ... SELECT instead.
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)
answered Sep 13 at 9:05
Erik von Asmuth
18.3k62038
answered Sep 13 at 9:05
Erik von Asmuth
18.3k62038
answered Sep 13 at 9:05
Erik von Asmuth
18.3k62038
answered Sep 13 at 9:05
Erik von Asmuth
18.3k62038
18.3k62038
1
Thanks, this works if I am executing directly in Access, but given I am generating the input query via a VB script and executing it from there with and ("ADODB.Connection") (Provider=Microsoft.ACE.OLEDB.12.0), I get an 'Record(s) cannot be read; no read permission on 'MSysObjects'. Any other ideas? thanks a lot
– userod
Sep 13 at 9:32
Well, usetestTableinstead ofMSysObjectsas your dummy table if you're sure that always contains records, or create your own dummy table.
– Erik von Asmuth
Sep 13 at 9:35
add a comment |
1
Thanks, this works if I am executing directly in Access, but given I am generating the input query via a VB script and executing it from there with and ("ADODB.Connection") (Provider=Microsoft.ACE.OLEDB.12.0), I get an 'Record(s) cannot be read; no read permission on 'MSysObjects'. Any other ideas? thanks a lot
– userod
Sep 13 at 9:32
Well, usetestTableinstead ofMSysObjectsas your dummy table if you're sure that always contains records, or create your own dummy table.
– Erik von Asmuth
Sep 13 at 9:35
1
1
Thanks, this works if I am executing directly in Access, but given I am generating the input query via a VB script and executing it from there with and ("ADODB.Connection") (Provider=Microsoft.ACE.OLEDB.12.0), I get an 'Record(s) cannot be read; no read permission on 'MSysObjects'. Any other ideas? thanks a lot
– userod
Sep 13 at 9:32
Thanks, this works if I am executing directly in Access, but given I am generating the input query via a VB script and executing it from there with and ("ADODB.Connection") (Provider=Microsoft.ACE.OLEDB.12.0), I get an 'Record(s) cannot be read; no read permission on 'MSysObjects'. Any other ideas? thanks a lot
– userod
Sep 13 at 9:32
Well, use
testTable instead of MSysObjects as your dummy table if you're sure that always contains records, or create your own dummy table.– Erik von Asmuth
Sep 13 at 9:35
Well, use
testTable instead of MSysObjects as your dummy table if you're sure that always contains records, or create your own dummy table.– Erik von Asmuth
Sep 13 at 9:35
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f52309940%2fms-access-insert-where-not-exists%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
I'm sorry about the format of the table. I am still not sure how to paste a table onto here (any help there would also be appreciated..)
– userod
Sep 13 at 8:54