Search code examples
androidsqlitekotlinandroid-sqlite

SQLite replace a row in one database from a row in another database

I have two database files each with a common single table makeup. I'd like to replace an entire table row in the first database with the similar row from the second database when a column in the second database has a flag set. I know how to Attach one database to another but I can't figure out the proper UPDATE SET instruction. I'm looking for string instruction to pass in a kotlin based android studio android.database.sqlite execSQL call.

For example: The table in each database has five columns named - name,address,date,value,flag I open the main database (main.db) and call ATTACH DATABASE to attach secondary database (secondary.db) as 'db2' I then want to find a row in the main database that matches a row in the secondary database where the name and address fields match and the flag field in the secondary database contains a value of 1.
I then want to replace the main database row with the row from the secondary database

I unsuccessfully tried variations of the following string command for execSQL:

val TABLE_NAME = "users"

val COL_NAME = "name"

val COL_ADR = "address"

val COL_DATE = "date"
    
val COL_VAL = "value"
    
val COL_FLG = "flag"

val db = this.writableDatabase

val attachDb = ("ATTACH DATABASE '$secondaryDbFile' AS 'db2'")            
db.execSQL(attachDb) 

val updateRows = ("UPDATE $TABLE_NAME SET ($COL_NAME, $COL_ADR, $COL_DATE, $COL_VAL, $COL_FLG) FROM db2.$TABLE_NAME WHERE $COL_FLG = 1")
db.execSQL(updateRows)

I don't have a good enough grasp of SQLite to construct the proper instruction, please help.

Solution

  • The syntax that you use in the UPDATE statement is wrong.
    You can use ROW VALUES and update only the columns date, value and flag since the other 2 columns must match.
    Also use aliases for the 2 tables (like t1 and t2) to distinguish the columns that have the same names:

    val db = this.writableDatabase
val attachDb = ("ATTACH DATABASE '$secondaryDbFile' AS db2") // no need for single quotes around db2
db.execSQL(attachDb)
val updateRows = "UPDATE $TABLE_NAME AS t1 " + 
                 "SET ($COL_DATE, $COL_VAL, $COL_FLG) = " +
                 "(SELECT t2.$COL_DATE, t2.$COL_VAL, 1 FROM db2.$TABLE_NAME AS t2 WHERE (t2.$COL_NAME, t2.$COL_ADR) = (t1.$COL_NAME, t1.$COL_ADR) AND t2.$COL_FLG = 1) " +
                 "WHERE EXISTS (SELECT 1 FROM db2.$TABLE_NAME AS t2 WHERE (t2.$COL_NAME, t2.$COL_ADR) = (t1.$COL_NAME, t1.$COL_ADR) AND t2.$COL_FLG = 1);"
db.execSQL(updateRows)

    For versions of SQLite that do not allow an alias for the updated table, use this statement:

    val updateRows = "UPDATE $TABLE_NAME " + 
                 "SET ($COL_DATE, $COL_VAL, $COL_FLG) = " +
                 "(SELECT t2.$COL_DATE, t2.$COL_VAL, 1 FROM db2.$TABLE_NAME AS t2 WHERE (t2.$COL_NAME, t2.$COL_ADR) = ($TABLE_NAME.$COL_NAME, $TABLE_NAME.$COL_ADR) AND t2.$COL_FLG = 1) " +
                 "WHERE EXISTS (SELECT 1 FROM db2.$TABLE_NAME AS t2 WHERE (t2.$COL_NAME, t2.$COL_ADR) = ($TABLE_NAME.$COL_NAME, $TABLE_NAME.$COL_ADR) AND t2.$COL_FLG = 1);"

    The syntax of the UPDATE statement could be simplified if the version of SQLite was 3.33.0+, but it would not work in Android which up till now, in API Level 31 supports version 3.32.2.

    This is the simplified version of the UPDATE statement (for future readers):

    val updateRows = "UPDATE $TABLE_NAME AS t1 " + 
                 "SET ($COL_DATE, $COL_VAL, $COL_FLG) = (t2.$COL_DATE, t2.$COL_VAL, 1) " +
                 "FROM db2.$TABLE_NAME AS t2 WHERE (t2.$COL_NAME, t2.$COL_ADR) = (t1.$COL_NAME, t1.$COL_ADR) AND t2.$COL_FLG = 1;"