Update/Replace two different database table column

Question

I have Two different-different Database, Databse1 and Database2, In this both database contain same name table "tblSubUnit" and this table have one column name is "Favorite", Now I want to replace Database1 Table column with Database2 table column(Favorite). So please help me how to Replace/Update this.

I Have create a function for this to attach this two database, But then after I was not able to Update/Replace column so please help me thanks in advance.

Code :

- (void)UpdateFavoriteData:(NSString*)query{

    NSString * language = [[NSLocale preferredLanguages] objectAtIndex:0];

    NSString *path = [[NSBundle mainBundle] pathForResource:language ofType:@"lproj"];

    if(path==nil){
        path = [[NSBundle mainBundle] pathForResource:@"Base" ofType:@"lproj"];
    }
    NSString *l_DatabasePathFromApp = [path stringByAppendingPathComponent:self.m_DatabaseName];

    sqlite3 *bookLoansDB;
    if (sqlite3_open([self.m_DatabasePath UTF8String], &bookLoansDB) == SQLITE_OK) {
        NSString *attachSQL = [NSString stringWithFormat: @"ATTACH DATABASE \'%s\' AS FavoriteData", [l_DatabasePathFromApp UTF8String]];
        char *errorMessage;
        if (sqlite3_exec(bookLoansDB, [attachSQL UTF8String], NULL, NULL, &errorMessage) == SQLITE_OK) {
            sqlite3_stmt *selectStmt;

            NSString *selectSQL = @"UPDATE [FavoriteData.SubUnit], [main.SubUnit] SET [FavoriteData.SubUnit].[Faverite] = [main.SubUnit].[Faverite]";
            if (sqlite3_prepare_v2(bookLoansDB, [selectSQL UTF8String] , -1, &selectStmt, nil) == SQLITE_OK) {
                if(sqlite3_step(selectStmt) == SQLITE_DONE) {
                }
            }
            else {
                NSLog(@"Error while Update statement: '%s'", sqlite3_errmsg(bookLoansDB));
            }
        }
        else {
            NSLog(@"Error while attaching databases: '%s'", errorMessage);
        }
    }
    else {
        sqlite3_close(bookLoansDB);
    }

}

I Was trying so many Update Query but same result got every time "Error while Update statement" so please help me how to Update One database table column with another database table column data.

possible duplicate of [SQL - Update a table using a field of another table](http://stackoverflow.com/questions/5930698/sql-update-a-table-using-a-field-of-another-table) — CL., Mar 04 '15 at 11:29
No, it was different Read my question carefully. I have Two Different Database. — Dipen Chudasama, Mar 04 '15 at 11:57
Yes, if you know about this, or you have a solution please post answer, I will appreciate, it was not helpful for me, I was already try it. — Dipen Chudasama, Mar 04 '15 at 12:00
Your syntax is wrong. The syntax in the answer I linked to is correct. You have, of course, to use the correct table/column names. — CL., Mar 04 '15 at 12:05

score 0 · Accepted Answer · answered Mar 05 '15 at 05:30

I got the solution, I finally got the proper query for this. see below answer.

- (void)UpdateFavoriteData:(NSString*)query{

    NSString * language = [[NSLocale preferredLanguages] objectAtIndex:0];

    NSString *path = [[NSBundle mainBundle] pathForResource:language ofType:@"lproj"];

    if(path==nil){
        path = [[NSBundle mainBundle] pathForResource:@"Base" ofType:@"lproj"];
    }
    NSString *l_DatabasePathFromApp = [path stringByAppendingPathComponent:self.m_DatabaseName];


    sqlite3 *bookLoansDB;
    if (sqlite3_open([self.m_DatabasePath UTF8String], &bookLoansDB) == SQLITE_OK) {
        NSString *attachSQL = [NSString stringWithFormat: @"ATTACH DATABASE \'%s\' AS FFavorite", [l_DatabasePathFromApp UTF8String]];
        char *errorMessage;
        if (sqlite3_exec(bookLoansDB, [attachSQL UTF8String], NULL, NULL, &errorMessage) == SQLITE_OK) {
            sqlite3_stmt *selectStmt;

            NSString *selectSQL = @"update FFavorite.SubUnit set Faverite = (SELECT main.SubUnit.Faverite FROM main.SubUnit WHERE FFavorite.SubUnit.unit_id = main.SubUnit.unit_id)";
            if (sqlite3_prepare_v2(bookLoansDB, [selectSQL UTF8String] , -1, &selectStmt, nil) == SQLITE_OK) {
                if(sqlite3_step(selectStmt) == SQLITE_DONE) {
                }
            }
            else {
                NSLog(@"Error while creating select statement: '%s'", sqlite3_errmsg(bookLoansDB));
            }
        }
        else {
            NSLog(@"Error while attaching databases: '%s'", errorMessage);
        }
    }
    else {
        sqlite3_close(bookLoansDB);
    }

}

Update/Replace two different database table column

1 Answers1