Trying to use the EXISTS keyword in SQL statement

[Bigdoggit]

I am trying to use the EXISTS keyword and for some reason cannot get it to work. I keep getting an error saying "Item not found in this collection" however without the EXISTS section everthing works fine. Also, the EXISTS section uses nothing new in terms of fields. Here is the SQL I am trying to use:




sSQL = "INSERT INTO [Supervisor_Changes] ([Test Case ID],[Web Verifier Name],[Web Verifier Execution Date]) SELECT '102-11 00030-E2E','Total_Power','4/28/2011 1:40:54 PM' FROM dual WHERE NOT EXISTS (SELECT * FROM [Supervisor_Changes] WHERE [Test Case ID]='102-11 00030-E2E');"

Just this works:

sSQL = "INSERT INTO [Supervisor_Changes] ([Test Case ID],[Web Verifier Name],[Web Verifier Execution Date]) VALUES ('102-11 00030-E2E','Total_Power','4/28/2011 1:40:54 PM');"

To run the sSQL statement I use:

db.Execute (sSQL)

which actually triggers the error. Any help is appreciated. I have googled the heck out of this and used samples but cannot get anything to work.

[orange]

A few comments based on just a quick read of your post.
Interesting SQL - very Oracle - like. Dual is an Oracle feature -- not Access.
If this is a new database, you should not be starting off with spaces in your column names. It appears that you are putting too much significance into your coding structure
eg 102-11 00030-E2E. One field, one fact is a tried and true approach.

As for the EXISTS, it is unclear what your intention is. If you are trying to prevent duplicates,and that is just a guess, then put a primary key on your table. For that I would highly recommend an autonumber that will guarantee uniqueness.

Your just this works example is a perfectly valid INSERT query for Access.

For db.execute, you would normally use

db.execute yourSQL,dbFailOnError and
you would have defined db and set it to current database prior to the attempt to execute.

You haven't showed any vba, so if you do not have an error trapping routine, the recommendation is to do so. The dbfailOnError will pass any error to your ON Error routines.

I'm not sure what comments you were anticipating, but hopefully these are constructive and helpful.

For SQL using EXISTS
http://www.techonthenet.com/sql/exists.php

Good luck

[Bigdoggit]

I do have primary keys. The spaces in the names are due to another individual creating field names and asking me to do the heavy lifting. They do work when I am busy and toss it in my lap. They also like to have spaces. That education is a work in progress.

I wondered about dual - the examples I saw looked like they might be Oracle. At first I wasn't using dual but tried it when nothing else worked. In the meantime I have a SELECT query search for a record containing the data I want to insert and if it is there running an UPDATE otherwise running an INSERT. You are right that the primary key prevents duplicates but the other individual doesn't always use primary keys and this procedure will be used a lot so I wanted to make it more robust if possible.

It all boils down to:

looking at my information, what is a proper INSERT using the EXISTS keyword? I know I have specific values - that is just to get things working. Once it works I have variables that will be substituted.

[orange]

Did you look at the reference I provided on use of EXISTS? It has a sample of INSERT with EXISTS.
Why is the use of EXISTS so important to your INSERT query? Not all INSERTs involve NOT EXISTS.

Here is a link to subqueries that should be helpful. It includes the Use of Exists Not Exists in searches.
http://allenbrowne.com/subquery-01.html

[Bigdoggit]

Should have mentioned that - yes I did, looked at your new one too. I can get a SELECT query to run no problem but for the life of me cannot get an INSERT INTO. I have specific values that will be added to the database so I am not sure how exactly to use the example provided on:

http://www.techonthenet.com/sql/exists.php

That page was one I consulted early on actually but still feel stumped.

[orange]

If you are working with vba, then a common approach to
"whether to Insert or Update" is by using Dcount

If Dcount( your criteria) = 0 then
'do the INSERT since it doesn't yet exist
else if DCOUNT( your criteria) = 1 then
'do an update since the record exists


**and if DCOUNT >1 you'll have to decide what that means-- here is a duplicate now???
Are you getting an error message? Have you verified your syntax?

I'm still not sure why you are as intent as you are for using EXISTS. Perhaps someone else has some ideas.
Good luck

[Bigdoggit]

Just for clarity - my understanding of the EXISTS statement is it will tell if any records match the subquery. So, I should be able to run an SQL INSERT INTO statement and have it only insert records if an EXISTS subquery returns no records. I want the subquery to look for any records that already contain the data I am trying to add. If so then the query should simply not perform any insert.

[orange]

I don't think it works exactly as you're trying to use it. Often the subquery uses a different table trying to match some field value in your main query. Sometimes the subquery does some aggregation of the same table as the main query but uses an alias. I can't find an example of INSERT Into that uses the EXIST on the main table.

Have you got your INSERT sql without the Dual... organized? Can you post the SQL with the Exists as you tried it?

I would recommend that you use vba and DCount.

Your

sSQL = "INSERT INTO [Supervisor_Changes] ([Test Case ID],[Web Verifier  Name],[Web Verifier Execution Date]) VALUES ('102-11  00030-E2E','Total_Power','4/28/2011 1:40:54 PM');"

is perfectly valid, and is what you would use once you have determined that DCount = 0

I haven't seen your Update sql, but that's what you'd use if DCount = 1

You can try this in the immediate window.

?DCount("*","Supervisor_Changes","[Test Case ID]='102-11 00030-E2E'")

This should return 0 if the record doesn't exist (set up to do INSERT)
and 1 if the record does exist (set up to do UPDATE)