CODEWITHSUNDEEP
sqlms-access
reguieg younes
reguieg younes

Reputation: 73

insert into if not exists ms-access

INSERT INTO table_a ( name ) VALUES (@name_)
WHERE NOT EXISTS (select name from table_a where name=@name_);

error : Query input must contain at least one table or query


and this doesn't work

INSERT INTO table ( name ) select @name_
WHERE NOT EXISTS (select name from table where name=@name_);

Upvotes: 1

Views: 1097

Answers (3)

forpas
forpas

Reputation: 164194

Instead of VALUES you should use SELECT but even then Access wouldn't allow SELECT without FROM.
So the workaround is to use after FROM a query that returns always exactly 1 row, like:

SELECT MIN(id) FROM table_a

Instead of MIN() you could use FIRST().
So your code should be:

INSERT INTO table_a([name]) 
SELECT @name_
FROM (SELECT MIN(id) FROM table_a) AS t
WHERE NOT EXISTS (SELECT 1 FROM table_a WHERE [name] = @name_);

Upvotes: 1

zip
zip

Reputation: 4061

Do it this way:

INSERT INTO table ( name ) select @name_
WHERE NOT EXISTS (select name from table where name=@name_);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270993

Just create a unique index on name:

create unique index unq_t_name on t(name);

Your insertion will generate an error if you attempt to insert a duplicate.

Upvotes: 1

Related Questions