Insert Statement with NOT IN Subquery

Question

I am trying to Update a row if it exists, otherwise insert (on duplicate key update) in MS Access, with the answer from this question SQL access query- Update row if exists, insert if does not

My insert query has the form:

INSERT INTO mytbl(id, title, notes) 
(SELECT 10, 'test', 'a'
From mytbl
WHERE 10 Not IN (select id from mytbl)
)

But I get an SQL syntax error. What am I missing here?

Answer 1

You don't need the parentheses around the outside SELECT:

INSERT INTO mytbl(id, title, notes) 
SELECT TOP 1 10, 'test', 'a'
FROM DummyTableWithExactlyOneRecord
WHERE 10 Not IN (select id from mytbl)

To handle the duplicate key error, I added the TOP 1 clause.

The way your query was originally written, if 10 is not an ID in mytbl, then the SELECT portion of your query would return a record for every existing row in mytbl. Because you are not referencing any fields in the table, they would all be identical rows. For example, if there were four rows in mytbl, you would have:

10    test    a
10    test    a
10    test    a
10    test    a

It would then try to do an insert for each of those returned rows. The first would succeed and the rest would fail with a duplicate key error. The TOP 1 clause says just use the TOP 1 record. Order does not matter in this case. Since all your fields are literals, every row will be the same.

Note that you could accomplish the same thing by substituting DISTINCT for TOP 1. I would expect the performance to be worse, though, unless the db engine is smart enough to realize up front that every row will be identical.

EDIT: DummyTableWithExactlyOneRecord is a local table with exactly one record. As @onedaywhen pointed out in his comment below, if there are no records at all in the table you are SELECTing from, then no record will be inserted.

Note that if you can guarantee that your dummy table will always have exactly 1 record, then the TOP 1 clause is not necessary. However, I've left it in because I think it makes the intent more obvious to a reader who may not know the contents of the dummy table (I'm assuming you will be naming your dummy table something shorter than DummyTableWithExactlyOneRecord).

Answer 2

I suppose u are looking for ID and not 10

try this

WHERE id NOT IN (SELECT id FROM mytbl)

Answer 3

What is intriguing here is that you are trying to insert constant values into a table while also validating the the UNIQUE INDEX (or PK) doesn't already exist. Thus your WHERE clause has to be structured in such a manner that it yields a FALSE condition and thus doesn't generate a row to be inserted.

Have you considered the following SQL:

INSERT INTO mytbl(id, title, notes) 
SELECT 10, 'test', 'a'
From mytbl
WHERE id NOT IN (SELECT id FROM mytbl WHERE id = 10)

Typically constant values are are inserted using:

INSERT INTO mytbl(id, title, notes) VALUES (10, 'test', 'a');

If this is psuedo code for a form that is populating a table with SQL Pass Through or something along those lines it might be easier to just trap the error code for the PK violation and report the error back to the user.

Hope this helps.

Answer 4

SQL DDL:

CREATE PROCEDURE AddMyStuff 
(
 :id CHAR(10), 
 :title VARCHAR(15), 
 :notes VARCHAR(100) = '{{NONE}}'
)
AS
INSERT INTO mytbl (id, title, notes) 
SELECT :id, :title, :notes
  FROM MySystemTableAlwaysHasExactlyOneRow
 WHERE :id NOT IN (SELECT id FROM mytbl);

SQL DML:

EXECUTE AddMyStuff 10, 'test', 'a';