Can you set a column equal to another column in a table using SQLiteDatabse update()?

Question

I need to make a column equal to another column in the table. I can't figure it out using the update method of my SQLiteDatabase.

I know the SQL statement is:

UPDATE coolTable SET columnA = columnB;

Do I put it in the ContentValues I pass the function? or the selection string?

thanks. I got it to work using the content resolver's update, but I was wondering if it is possible to use the database's update() function instead of execSQL(). It says in the documentation: " you're encouraged to use insert(String, String, ContentValues), update(String, ContentValues, String, String[]), et al, when possible." — user2264210, Apr 26 '18 at 09:19
no, you cannot use `update` in that particular case - it is used for syntax like `update tableName set column_1 = new_value_1, ...` where `new_value_1` is a constant value passed in `ContentValues` — pskink, Apr 26 '18 at 09:30

score 1 · Accepted Answer · answered Apr 26 '18 at 09:37

1

You can use update() only to set literal values (as bind params), not any other kind of expression supported by sqlite SQL syntax.

Use execSQL() to execute your raw UPDATE query with column name expression.

answered Apr 26 '18 at 09:37

laalto

150,114
66
286
303

Jon Goodwin · Answer 2 · 2018-04-26T22:10:37.613

execSQL() with error checking:

SQLiteDatabase db = getReadableDatabase();

try {
    long count = DatabaseUtils.queryNumEntries(db, "pweb");
    db.execSQL("update pweb set h_id = _id;");
    long rows = DatabaseUtils.longForQuery(db, "SELECT changes()", null);
    if(count != rows ) Log.e("wrong count","update failed");
 }
catch(SQLException e){
  Log.e("SQLException","update failed");            
}  

db.close();

but I was wondering if it is possible to use the database's update() function instead of execSQL()

You can use sqlite3 to view and manipulate you data, and test out sql commands.

Create a table:

CREATE TABLE pweb (_id INTEGER PRIMARY KEY,h_id  INTEGER ,sent  INTEGER);

See the original rows:

sqlite> select * from pweb;
1|10|1
2|20|1
3|30|0
4|40|0

execute a column update:

sqlite> update pweb set h_id = _id;

See the changes to all rows:

sqlite> select * from pweb;
1|1|1
2|2|1
3|3|0
4|4|0

Something more complex ?

sqlite>  update pweb set h_id = _id + 1;

result:

sqlite> select * from pweb;
1|2|1
2|3|1
3|4|0
4|5|0

See also Understanding SQLITE DataBase in Android:

Can you set a column equal to another column in a table using SQLiteDatabse update()?

2 Answers2