Delete current row in sqlite3, iterating over a result set

Question

Is it possible to iterate over a set of results, and if the condition is met, delete the current row?

i.e. sort of

int rc;
sqlite3_stmt* statement;
sqlite3_exec(db, "BEGIN", 0, 0, 0);

sqlite3_prepare_v2(_db, "SELECT id,status,filename,del FROM mytable", -1, &statement, NULL);

rc = sqlite3_step(statement);
while (rc == SQLITE_ROW){
  int id = sqlite3_column_int(statement, 1);
  int status = sqlite3_column_int(statement, 2);
  const unsigned char* filename = sqlite3_column_int(statement, 3);
  int del = sqlite3_column_int(statement, 4);

  if (status == 0 || del > 0){
    int rc = unlink(filename);
    if (rc == 0)
      // Now delete the current row
    else 
      // unlink failed, find out why, try again or ... ?
  } 

  rc = sqlite3_step(statement);
}
sqlite3_finalize(statement);
sqlite3_exec(db, "COMMIT", 0, 0, 0);

I could just call a single sql statement to delete all rows that match the criteria, but I don't want to do this if for some reason the failure is turned off.

Is it possible to call an operation to delete the current line?

EDIT: So there is a special column called rowid. Am I just adding this as a column to the previous statement and create another statement like "delete from table, where rowid =?" and pass the current line?

Should this work correctly? Is this the best way around this?

Answer 1

, , , . - , , ( ) :

• , , del > 0 (SELECT id,status,filename,del FROM mytable WHERE del > 0). , . , del.

• , : DELETE FROM table WHERE id IN (?), - , , . , , , ( 1000, 5000 ..); SQLite, , .

• SQLite , :

  void deleteFileFunc(sqlite3_context * context, int argc, sqlite3_value ** argv) {
    assert(argc == 1);
    const char * fileName = sqlite3_value_text(argv[0]);
    int rc = unlink(fileName);
    sqlite3_result_int(context, rc);
  }
  sqlite3_create_function(db, "delete_file", 1, SQLITE3_UTF8, NULL, &deleteFileFunc, NULL, NULL);
  

DELETE FROM mytable WHERE del > 0 AND delete_file(filename) == 0. , , . SQLite 3 : http://www.sqlite.org/c3ref/create_function.html