Hi I need to write Insert Query in SQLite3 in iPhone

Question

Hi need insert data in table if the record is not already exits Ex: IF NOT EXISTS (SELECT Id FROM table WHERE id=_Id) THEN
INSERT INTO tbale(.....)

This can be easily done using stored procedure in MySql. But I want to d same thing in SQLite by writing a single query statement.

Pleas help

Answer 1

SQlite doesn't have stored procedudes that you need to do logic like this. But you can always extend sqlite with simple C-functions. Or you could simply code this logic in whatever language you are writing your program in. I don't think the performance hit is that great. Did your profiling show that this is a critical path that needs to be optimized?

Answer 2

Put a unique index on the id column (or make it the primary key) then use:

REPLACE INTO table(.....)

see: http://www.sqlite.org/lang_insert.html or http://www.sqlite.org/lang_replace.html

Answer 3

I would do this by running a first query to see if the record exists then decide whether to run an insert or update. I don't think SQLite has a fully featured query language to support the desired shorter approach.

Answer 4

Hi, I found a way its: INSERT INTO CategoryMaster (CategoryID, CategoryText) SELECT %d,'%@' WHERE NOT EXISTS (SELECT 1 FROM CategoryMaster WHERE CategoryID= %d)

Its working for me :)