CODEWITHSUNDEEP
sqlobjective-csqlite
Display Name
Display Name

Reputation: 1035

sql checking to see if row exists issues

I am trying to make an sql statement where it checks a table to make sure a row dosnt already exist, i have attempted it with the following code but don't seem to work.

+ (void) AddToCOLDatabase : (NSString*) AgeRange : (NSString*) MainArea : (NSString*) SubArea : (NSString*) Framework{

        NSString* updateSQL;
        sqlite3_stmt* updateStmt;

        sqlite3_open([dbObservationPathString UTF8String], &ObservationDB);

        updateSQL = [NSString stringWithFormat: @"IF NOT EXISTS (SELECT * FROM OBSERVATIONS WHERE LEVEL0 = '%@' AND LEVEL1 = '%@' AND LEVEL2 = '%@' AND LEVEL3 = '%@') INSERT INTO OBSERVATIONS(LEVEL1,LEVEL2,LEVEL3, LEVEL0) values (?,?,?,?)",AgeRange, MainArea, SubArea,Goal];


        sqlite3_prepare_v2(ObservationDB, [updateSQL UTF8String], -1, &updateStmt, NULL);
        sqlite3_bind_text(updateStmt, 1, [AgeRange UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(updateStmt, 2, [MainArea UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(updateStmt, 3, [SubArea UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(updateStmt, 4, [Framework UTF8String], -1, SQLITE_TRANSIENT);

        sqlite3_step(updateStmt);
        sqlite3_finalize(updateStmt);
        sqlite3_close(ObservationDB);

   }

the above code dosnt work the database isn't changed, i just wondered if anyone could give me a nudge in the correct direction, also id like to modify if so the primary key OBSERVATIONSID is also returned if an entry is made, i know it'll have something to do with OUTPUT but don't know anymore than that Thanks

Upvotes: 0

Views: 90

Answers (1)

rmaddy
rmaddy

Reputation: 318814

You can use INSERT OR REPLACE:

INSERT OR REPLACE INTO OBSERVATIONS(LEVEL1,LEVEL2,LEVEL3, LEVEL0) values (?,?,?,?)

For this to work as desired, you need a unique index on the field(s) that shouldn't be duplicated.

Upvotes: 1

Related Questions