Insert with NOT EXISTS subquery

[elziko]

I'm trying to insert a record only if it doesn't already exist:

INSERT INTO protectedfiles (filename)
VALUES ('New Text Document.txt')
WHERE NOT EXISTS
(SELECT filename
FROM protectedfiles
WHERE filename 'New Text Document.txt');

However I am just told that my SQL command is not ended properly.

Where am I going wrong??

Cheers,

elziko

 

[dickiebird]

You need =
on the last line
between filename and 'New Text Document.txt
ie
INSERT INTO protectedfiles (filename)
VALUES ('New Text Document.txt')
WHERE NOT EXISTS
(SELECT filename
FROM protectedfiles
WHERE filename = 'New Text Document.txt');

Dickie Bird
db@dickiebird.freeserve.co.uk

 

[Dagon]

You can't do what you are trying to do. A values clause refers to a set of data elements. You can't add a "where not exists" to this.

You have to use "where not exists" with a select statement i.e.

INSERT INTO protectedfiles (filename)
select 'New Text Document.txt'
FROM DUAL
WHERE NOT EXISTS
(SELECT filename
FROM protectedfiles
WHERE filename = 'New Text Document.txt');

I suspect in reality you will be getting the filename from another table so it will look something like:

INSERT INTO protectedfiles (filename)
select filename
FROM new_files_table n
WHERE NOT EXISTS
(SELECT filename
FROM protectedfiles p
WHERE n.filename = p.filename)