Sqlite not updating rows

Question

Hi I'm trying to update some rows incrementing a value in an int field but it doesn't work. Here my snippet of code:

        SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
        queryBuilder.setTables("TICKET");
        String[] proj = new String[]{ "TICKET","NUMERO_ARTICOLO","QUANTITA","COMMESSA_IN","CODICE_SQUADRA"};
        try{
            Cursor cursor = queryBuilder.query(sqlDB, proj, selection, null, null, null, null);
            sqlDB.beginTransaction();
            cursor.moveToFirst();
            while (cursor.moveToNext()){
                String query =     "UPDATE MATERIALE "
                        + " SET RIMANENTE = RIMANENTE - " + cursor.getString(cursor.getColumnIndex("QUANTITA"))
                        + " WHERE NUMERO_ARTICOLO = '"+cursor.getString(cursor.getColumnIndex("NUMERO_ARTICOLO"))+"'"
                        +" AND COMMESSA = '"+cursor.getString(cursor.getColumnIndex("COMMESSA_IN"))+"' "
                        +" AND CODICE_SQUADRA = '"+cursor.getString(cursor.getColumnIndex("CODICE_SQUADRA"))+"' ";
                Log.d("update","Query: "+query);
                sqlDB.rawQuery(query, null);
            }
            rowsUpdated = sqlDB.update("TICKET", values, selection, null);
            sqlDB.setTransactionSuccessful();
            Log.d("update","Aggiornate "+rowsUpdated+" righe");
            sqlDB.endTransaction();
        }catch (Exception e) {
            e.printStackTrace();
        }

        break;
    default:
        throw new IllegalArgumentException("Unknown URI: " + uri);
    }
    getContext().getContentResolver().notifyChange(CONTENT_URI, null);
    return rowsUpdated;

Last query is working

rowsUpdated = sqlDB.update("TICKET", values, selection, null);

but the while cycle is not working. What I do wrong? Maybe the UPDATE SET RIMANENTE = RIMANENTE - " + cursor.getString(cursor.getColumnIndex("QUANTITA"))

I want the cycle (while) and the last update in the same transaction.

Answer 1

Sqlite uses file-based locking while you are inserting-updating or selecting the rows from database. This means when you are fetching the records from database using Select query and until the cursor with that select statement is not closed you can not insert update or delete in mean time.
So you should close the cursor and then use execSQL in place of rawQuery to fire update

    try
    {
        Cursor cursor = queryBuilder.query(sqlDB, proj, selection, null, null, null, null);
        //sqlDB.beginTransaction();
        List<String> updateSql = new ArrayList<String>();
        try
        {
            cursor.moveToFirst();
            while (cursor.moveToNext())
            {
                updateSql.add(    "UPDATE MATERIALE "
                        + " SET RIMANENTE = RIMANENTE - " + cursor.getString(cursor.getColumnIndex("QUANTITA"))
                        + " WHERE NUMERO_ARTICOLO = '"+cursor.getString(cursor.getColumnIndex("NUMERO_ARTICOLO"))+"'"
                        +" AND COMMESSA = '"+cursor.getString(cursor.getColumnIndex("COMMESSA_IN"))+"' "
                        +" AND CODICE_SQUADRA = '"+cursor.getString(cursor.getColumnIndex("CODICE_SQUADRA"))+"' ");
                Log.d("update","Query: "+query);                    
            }
        } 
        finally
        {
            cursor.close();
        }
      try
      {
        sqlDB.beginTransaction();
        for (String query : updateSql)
        {
            sqlDB.execSQL(query, null); //Here it should be execSQL instead of rawQuery
        }
        rowsUpdated = sqlDB.update("TICKET", values, selection, null);

        sqlDB.setTransactionSuccessful();
        Log.d("update","Aggiornate "+rowsUpdated+" righe");
      }
      finally
      {
        sqlDB.endTransaction();
      }
    }
    catch (Exception e) 
    {
        e.printStackTrace();
    }

EDIT If I read documentation it is clearly mentioned that

For UPDATE statements, use any of the following instead.
update(String, ContentValues, String, String[])
updateWithOnConflict(String, ContentValues, String, String[], int)

Answer 2

You'd better use SQL facilities to accomplish this task:

try {
    sqlDB.execSQL("UPDATE TICKET SET RIMANENTE = RIMANENTE - QUANTITA");
} catch (Exception e) {
    e.printStackTrace();
}

This will, in a single statement - so, in a single transaction also - subtract QUANTITA to RIMANENTE in all rows from table TICKET.

EDIT:

UPDATE TICKET SET RIMANENTE = RIMANENTE - QUANTITA WHERE QUANTITA IS NOT NULL

is preferable, because if QUANITTA is not defined, i.e. NULL, RIMANENTE will be nulled. WHERE clause prevents such from happen.