How to check and then insert in sqlite?

you can do easily "insert or ignore into tbl_name" here you can see the example http://www.raywenderlich.com/913/sqlite-tutorial-for-ios-making-our-app this would be usefull for you.... http://www.sqlite.org/lang_conflict.html

You can do INSERT OR REPLACE if you have a primary key on the table. For example:

sqlite3 *database = NULL;

NSString *documentsPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];
NSString *path = [documentsPath stringByAppendingPathComponent:@"test.sqlite"];

int rc = sqlite3_open([path UTF8String], &database);
NSAssert(rc == SQLITE_OK, @"Open failed");

// note, use PRIMARY KEY when creating table

rc = sqlite3_exec(database, "CREATE TABLE IF NOT EXISTS test (animal TEXT PRIMARY KEY, sound TEXT)", NULL, NULL, NULL);
NSAssert(rc == SQLITE_OK, @"Create failed: %s", sqlite3_errmsg(database));

// create a record that will be replaced by the subsequent `INSERT OR REPLACE`

rc = sqlite3_exec(database, "INSERT INTO test (animal, sound) VALUES ('dog', 'meow')", NULL, NULL, NULL);
NSAssert(rc == SQLITE_OK, @"INSERT failed: %s", sqlite3_errmsg(database));

// this will REPLACE entry if value with same PK found, otherwise it would INSERT

rc = sqlite3_exec(database, "INSERT OR REPLACE INTO test (animal, sound) VALUES ('dog', 'woof')", NULL, NULL, NULL);
NSAssert(rc == SQLITE_OK, @"INSERT failed: %s", sqlite3_errmsg(database));

// now retrieve values and make sure it worked like we thought it would

sqlite3_stmt *statement = NULL;
rc = sqlite3_prepare_v2(database, "SELECT animal, sound FROM test", -1, &statement, NULL);
NSAssert(rc == SQLITE_OK, @"prepare SELECT failed: %s", sqlite3_errmsg(database));

while ((rc = sqlite3_step(statement)) == SQLITE_ROW) {
    const unsigned char *animal = sqlite3_column_text(statement, 0);
    const unsigned char *sound  = sqlite3_column_text(statement, 1);
    NSLog(@"%s goes %s", animal, sound);
}
NSAssert(rc == SQLITE_DONE, @"step failed: %s", sqlite3_errmsg(database));

sqlite3_finalize(statement);

sqlite3_close(database);

And that will report that the INSERT OR REPLACE replaced the previous value rather than inserting second record:

2013-11-21 08:59:25.285 AnimalSounds[53549:70b] dog goes woof

If you don't have primary key, rather than this simple INSERT OR REPLACE, you'd have to break it into two steps, either:

1. Look for record with SELECT: If found, do UPDATE; if not found, do INSERT.

2. First DELETE any records that would match whatever criteria you want, and then do INSERT.

This first approach is a bit safer, but you could use the second approach if you had to (though you would probably use transactions a do a ROLLBACK if you had any problems). Needless to say, the INSERT OR REPLACE approach is even easier, but requires a primary key.

First call get record query in Database. Here I am add a example, I am checking that user login information available in database or not. So add below code. IF User record is available than i get record array otherwise nil.

 +(NSArray*)getTBL_LOGIN
{
    NSMutableArray *Favourite=[[NSMutableArray alloc]init];

 sqlite3 *database;

 TabBarAppDelegate *x=(TabBarAppDelegate*)[[UIApplication sharedApplication]delegate];
if(sqlite3_open([[x dataBasePath] UTF8String],&database) == SQLITE_OK) {
    NSString *str = [NSString stringWithFormat:@"select * from tbl_login"];
    const char *sqlStmt=[str UTF8String];             
    sqlite3_stmt *compiledStmt;                           
    if(sqlite3_prepare_v2(database, sqlStmt, -1, &compiledStmt, NULL) == SQLITE_OK) {
    while(sqlite3_step(compiledStmt)==SQLITE_ROW) 
            {
                NSString *uid=[NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStmt, 0)];

                NSString *username=[NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStmt, 1)];


                NSDictionary *d=[NSDictionary dictionaryWithObjectsAndKeys:uid,@"uid",username,@"username",nil];
        [Favourite addObject:d];
    }
    }
    sqlite3_finalize(compiledStmt);
}
sqlite3_close(database);

if([Favourite count]>0)
{
    NSArray *ar=[NSArray arrayWithArray:Favourite];
    return ar;
} else {
    return nil;
}
}

If you get the record count >=1 then record exist so you have to call update query if you get record count 0 than record is not available in database so you have to call insert query

In a situation where I imported all updates into another database table, I could use following:

-- Existing table: t(uc UNIQUE, v1, v2, v3);
-- Updates table: ut(uc UNIQUE, v2);
INSERT OR REPLACE INTO t
    SELECT ut.uc, et.v1, ut.v2, et.v3 FROM ut
    LEFT JOIN t AS et ON ut.uc=et.uc;

This statement will insert new rows from ut into t. Existing rows are replaced with a row containing new data from ut and existing data from t.

For this to work, you must have a UNIQUE column (which makes sense as you are looking for a row update or insert a new one), and have new data available so it can be queried (in same or another database).

This worked for me, hope it may help you.

Another solution, maybe with better performance is using two statements:

 UPDATE t SET v1='some value', v2=123 WHERE unique_col='some_id';
 INSERT OR IGNORE t(v1, v2, unique_col) VALUES('some value', 123, 'some_id');

UPDATE will become a null operation when 'some_id' is not found. INSERT will ignore all existent 'some_id'.