ios - 无法使用多个语句在 iOS 中的 sqlite 中将数据从一个表插入到另一个表

Question

我有两个表“table1”和“table_00”。我想将一些数据从 table1 移动到 table_00

我有这个方法来做到这一点:

- (void)moveData {


NSString *docsDir;
NSArray *dirPaths;

// Get the documents directory
dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
docsDir = dirPaths[0];

// Build the path to the database file

databasePath = [[NSString alloc]
                initWithString: [docsDir stringByAppendingPathComponent:
                                 @"MYDB_3.db"]];

 sqlite3_stmt    *statement;
const char *dbpath = [databasePath UTF8String];

if (sqlite3_open(dbpath, &myDatabase) == SQLITE_OK) {

    NSString *insertSQL=@"INSERT INTO TABLE_00 SELECT * FROM TABLE_1 WHERE ID=00000" ;
     const char *insert_stmt = [insertSQL UTF8String];

    //i am using only one 'insertSQL', i put two 'insertSQL' strings here for reference 
   //mulitple statements
   NSString *insertSQL=@"INSERT INTO TABLE_00 SELECT * FROM TABLE_1 WHERE UTILITYID='00000';"
                         "INSERT INTO TABLE_01 SELECT * FROM TABLE_1 WHERE UTILITYID='00001';"
                         "INSERT INTO TABLE_02 SELECT * FROM TABLE_1 WHERE UTILITYID='00002';"
    "INSERT INTO TABLE_03 SELECT * FROM TABLE_1 WHERE UTILITYID='00003';";

  //this multiple query statement is not working

    sqlite3_prepare_v2(myDatabase, insert_stmt, -1, &statement, NULL);
    if (sqlite3_step(statement) == SQLITE_DONE) {
                   NSLog(@"data moved");
    } else {
        NSLog(@"data not moved");
    }
    sqlite3_finalize(statement);
    sqlite3_close(myDatabase);
}

调用此方法后，我可以使用单个查询移动数据，但是我无法使用多个语句移动数据。您能告诉我为什么会这样吗？

提前致谢。

Answer 1

我想你有一个额外的，它应该是:

INSERT INTO TABLE_00 SELECT * FROM TABLE_1 WHERE ID= '00000'

更好的方法是使用占位符:

sqlite3_stmt *compiled = nil;
const char *sql = "INSERT INTO TABLE_00 SELECT * FROM TABLE_1 WHERE ID= ?";
sqlite3_prepare_v2(database, sql, -1, &compiled, NULL);
sqlite3_bind_text(compiled, 1, [yourId UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_step(compiled);
sqlite3_finalize(compiled);