CODEWITHSUNDEEP
androidsqlite
Peri Hartman
Peri Hartman

Reputation: 19484

how to update sqlite using expression?

I would like to do this query in SQLite:

  update table1 set col1 = (? || substr (col1, col2))
  where table1.id in (select id from table2 where condition);

I'm not sure how to do this. SQLiteDatabase.rawQuery doesn't work. All the other APIs I've seen don't allow the expression in the "set" part. If I could use SQLiteStatement, it would work. But that constructor is only visible in its package, not to my code :(

Ideally, I would do something like this:

String query = 
  "update Table1 set " +
  "  col1 = (? || substr (col1, col2)), " +
  "  col2 = ? " +
  "where Table1.id in " +
  "  (select id from Table2 where col3 = ?)";

String[] args = new String[3];
args[0] = arg0;
args[1] = arg1;
args[2] = arg2;

SQLiteStatement statement = new SQLiteStatement (getDb(), query, args);
int rowsUpdated = 0;
try
{
  rowsUpdated = statement.executeUpdateDelete();
} finally {
  statement.close();
}

Any ideas? Thanks.

Upvotes: 0

Views: 176

Answers (1)

Benito Bertoli
Benito Bertoli

Reputation: 25793

Usually when we want to run CRUD operations we use SQLiteDatabase.execSQL().

SQLiteDatabase.rawQuery() is generally used for select queries and it returns a Cursor with the result set.

Although rawQuery() should theoretically work because according to the docs

Runs the provided SQL and returns a Cursor over the result set.

But others have reported that it doesn't work with update queries, so I'm not entirely sure about that.

Upvotes: 1

Related Questions