SQLite Update Statement Possibilities in iOS SQLite3

2
votes

My iOS app uses SQLite3 databases and so far I have successfully managed to create tables, insert values and then later select them.

However, now I want to update. I tried this:

UPDATE field WHERE _id = 5 (name,type,state) VALUES (?,?,?)

But SQLite tells me a syntax error exists near the WHERE

As I understand that SQLite may not support the above syntax preferring it formatted as below.

UPDATE field  SET name= "Upper"  type= "Pigs" state=1 WHERE _id = 5;

However notice in the second statement there are no (?,?,?) (parameterized query I believe its called) for dynamically inserting values into the SQL string. Tell me is this possible and if so how?

2
iossqlitesql-update

2 Answers

4
votes

Yes You can use Parametrized Query as Below :-

NSString *sqlString = @"UPDATE field SET name=?, type=?, state=? where _id=?";

sqlite3_stmt *stmt;
if(sqlite3_prepare(database, [sqlString UTF8String], -1, &stmt, NULL) != SQLITE_OK)
{
  //Handle Error
}


if(sqlite3_bind_text(stmt, 1, [record.name UTF8String],[record.type UTF8String],[record.state UTF8String], -1, SQLITE_TRANSIENT) != SQLITE_OK)
{
  // Handle Error
}
if(sqlite3_bind_int(stmt, 2, record.key) != SQLITE_OK)
{
  // Handle Error
}


if (sqlite3_step(statement) != SQLITE_DONE)
{
  // Handle Error
}

And your Parametrized Code is ready.

3
votes

You want

update field set name = ?, type = ?, state = ? where id = 5