SQLLite, NOT EXISTS doesn't work

Question

Ok, let's say from start I'm not a SQL ninja. Anyway I'm using SQLite in a tiny project of mine. I've this test table:

CREATE TABLE [prova] 
(
    [id] INTEGER PRIMARY KEY UNIQUE,
    [str] TEXT NOT NULL
)

and this query won't work:

insert into prova (id, str)
    select null as id, "foo" as str
    where not exists (select * from prova where prova.str = "foo")

why this??? I need to do conditional inserts and I'm seeking a way to make them faster

Answer 1

I don't know SQLite, but in standard sql I don't think any where clause is valid for an insert statement

Answer 2

Are you just using this to create your database ? Because what i usually do is using MySQL to create the databases and then export them in my sqlite db. It's much easier and you can use "real sql" But if you have to insert in sqlite in-app that is not a good option and i can not help you then.

Answer 3

Actually, it does work ... Look at this sqlite3 session I just did, the final select only returns 1 row ...

tnt@lain ~ $ sqlite3 test.sqlite3
SQLite version 3.6.17
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE prova
   ...> (
   ...>         id INTEGER PRIMARY KEY UNIQUE,
   ...>         str TEXT NOT NULL
   ...> );      
sqlite> 
sqlite> insert into prova (id, str)
   ...>         select null as id, "foo" as str
   ...>         where not exists (select * from prova where prova.str = "foo");
sqlite> insert into prova (id, str)
   ...>         select null as id, "foo" as str
   ...>         where not exists (select * from prova where prova.str = "foo");
sqlite> insert into prova (id, str)
   ...>         select null as id, "foo" as str
   ...>         where not exists (select * from prova where prova.str = "foo");
sqlite> select * from prova;
1|foo
sqlite>