On 03/04/2011, at 6:49 PM, Marcelo S Zanetti wrote:

> I have an item to insert in the table which will be inserted only if this 
> item is not yet in that table otherwise I would like to return the item's key.
> 
> like that 
> 
> IF 1==SELECT COUNT(*) from table 
> WHERE item==new THEN SELECT itemID from tabel WHERE item==new ELSE 
> INSERT INTO table (item) VALUES (new)
> 
> It does not work in this way ... could somebody tell me please what is the 
> correct sintax or whether this is possible at all.

SQL is a set manipulation language, not a procedural language. So you write 
commands that affect a subset of data all at once. To accomplish your task, 
you'd write this:

insert into Table (Item) select new where new not in (select item from Table);
select ItemID from Table where Item = new;

Furthermore, if you are returning the key for some more manipulation, it's best 
done in the same SQL call, rather than manipulated in your application code 
only to be re-injected into the SQL from which it came.

Tom
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml



_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to