Update statement in sqlite

Question

Hey what i want to do is update a table if the field exsits or insert the new value as given by the user.

For this what i do is select everything present in the table and check if the name entered by the user exists den update it or else insert it.

However the problem is when there is no data in the table the query fails and no data is inserted in the table.

NSString *query = @"select * from PPM";
sqlite_stmt *statement;

if(sqlite_prepare_v2(database,[query UTF8string],-1,&statement,NULL) == SQLITE_OK)
{
    while (sqlite3_step(statement) == SQLITE_ROW)
     //select data from table  
     //if value entered is same as entered by user update the table
     //else insert in the table
}

Answer 1

How about INSERT OR REPLACE ? You can use it with the UNIQUE constraints in your database structure to replace a value if it already exists, and insert it if it not there already.

shell> sqlite3
SQLite version 3.6.23
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE user(name UNIQUE, value);
sqlite> INSERT OR REPLACE INTO user VALUES("foo", 123);
sqlite> SELECT * FROM user;
foo|123
sqlite> INSERT OR REPLACE INTO user VALUES("foo", 321);
sqlite> SELECT * FROM user;
foo|321

Answer 2

If you use the INSERT OR REPLACE variation of the INSERT command, you don't need to check for existing data, as SQLite will do it for you, as long as there is a column with a UNIQUE constraint.

For example:

CREATE TABLE db.table(id UNIQUE, name);

INSERT OR REPLACE INTO db.table (id, name) VALUES (5, 'exep');

If an id of 5 exists in the table, this command will behave like an update, else it will be an insert.

See http://www.sqlite.org/lang_conflict.html for more details.