Android SQLite incrementing a column value

Question

I'm trying to create a score database that increments the players 'score' by one when they win by calling updateScore(). The primary key and player number are identical (I may need to restructure the DB at some point) and the final column is 'score'.

Below is the code that initially sets the score (this works), the method that gets the score (also works fine) and the method that updates the score, incrementing the relevant players score by 1. This is the part the doesn't work, is there something I should be doing differently here? Thanks.

     /** Add a record to the database of two player scores
     * @param playerId
     * @param playerScore
     **/
    public void addScore (int playerId, int playerScore) {

        SQLiteDatabase database = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(ID, playerId);
        values.put(PLAYERNUM, playerId);
        values.put(SCORE, playerScore);

        database.insert(TABLE_2PSCORES, null, values);

        database.close();

    }


    // Get the score 
    public int getScore (int playerId) { 
        SQLiteDatabase database = this.getReadableDatabase();

        Cursor cursor = database.query(TABLE_2PSCORES, COLUMNS, " player = ?", new String[] {String.valueOf(playerId) }, null, null, null, null); //null = groupby, having, orderby, limit

        if (cursor !=null) { cursor.moveToFirst(); }

        int output = cursor.getInt(2);

        return output;
    }


    // Increment score by 1
    public void updateScore (int playerId) {

        SQLiteDatabase database = this.getWritableDatabase();

        int playerScore = getScore(playerId);
        int playerScoreInc = playerScore ++;

        ContentValues values = new ContentValues();
        values.put("score", playerScoreInc);

        database.update(TABLE_2PSCORES, values, PLAYERNUM+" = ?", new String[] {String.valueOf(playerId)} );

        database.close();

    }

Related question: http://stackoverflow.com/questions/9798473/sqlite-in-android-how-to-update-a-specific-row — reinierpost, Feb 26 '14 at 09:45
Look at this question. http://stackoverflow.com/questions/744289/sqlite-increase-value-by-a-certain-number — the_glory, Aug 26 '15 at 07:33

score 10 · Answer 1 · answered Feb 26 '14 at 09:39

int playerScoreInc = playerScore ++;

This assigns playerScore to playerScoreInc and only after that increments playerScore. To first increment and then assign, change to ++playerScore.

However, you can do it all in SQL, no need to fetch score, increment it in code and then update the database table separately:

database.execSQL("UPDATE " + TABLE_2PSCORES + " SET " + SCORE + "=" + SCORE + "+1" + " WHERE " + PLAYERNUM + "=?",
    new String[] { String.valueOf(playerId) } );

score 5 · Answer 2 · edited Jun 20 '20 at 09:12

5

The other answers solve the original question, but the syntax makes it hard to understand. This is a more general answer for future viewers.

How to increment a SQLite column value

SQLite

The general SQLite syntax is

UPDATE {Table} SET {Column} = {Column} + {Value} WHERE {Condition}

An example of this is

UPDATE Products SET Price = Price + 1 WHERE ProductID = 50

(Credits to this answer)

Android

Now that the general syntax is clear, let me translate that into Android syntax.

private static final String PRODUCTS_TABLE = "Products";
private static final String ID = "ProductID";
private static final String PRICE = "Price";

String valueToIncrementBy = "1";
String productId = "50";
String[] bindingArgs = new String[]{ valueToIncrementBy, productId };

SQLiteDatabase db = helper.getWritableDatabase();
db.execSQL("UPDATE " + PRODUCTS_TABLE +
        " SET " + PRICE + " = " + PRICE + " + ?" +
        " WHERE " + ID + " = ?",
        bindingArgs);
db.close();

TODO

This answer should be updated to use update rather than execSQL. See comment below.

edited Jun 20 '20 at 09:12

Community

1
1

answered Oct 22 '16 at 10:21

Suragch

484,302
314
1,365
1,393

The android docs says: Execute a single SQL statement that is NOT a SELECT/INSERT/UPDATE/DELETE. The issue is that execSql(..) does not return the number of affected rows. Any other solution? – eastwater Jan 10 '18 at 22:59
@Sunnyday, you're right. Good catch. It looks like I should be using [`update`](https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#update(java.lang.String,%20android.content.ContentValues,%20java.lang.String,%20java.lang.String[])) instead. – Suragch Jan 11 '18 at 22:12
1

But using update means you have to query the current score from the table before updating the same table. It would have been much easier to pass some DML to the `update` method. I still use the `execSql` for that reason. – TheRealChx101 Jul 24 '18 at 10:55

score 3 · Accepted Answer · answered Feb 26 '14 at 09:39

3

Change

int playerScoreInc = playerScore ++;

to

int playerScoreInc = ++ playerScore;

answered Feb 26 '14 at 09:39

igorshin

76
4

score 2 · Answer 4 · answered Feb 26 '14 at 09:41

2

I think this will work

 // Increment score by 1
    public void updateScore (int playerId) {

        SQLiteDatabase database = this.getWritableDatabase();

        int playerScore = getScore(playerId);
        int playerScoreInc = ++ playerScore;

        ContentValues values = new ContentValues();
        values.put("score", playerScoreInc);

        database.update(TABLE_2PSCORES, values, PLAYERNUM+" = ?", new String[] {String.valueOf(playerId)} );

        database.close();

    }

answered Feb 26 '14 at 09:41

Mohammad Ashfaq

1,333
2
14
38

Thank you for the response. A few answers reference the ++playerscore over playerscore++, and this has fixed my problem. Thanks again! – davidbain Feb 26 '14 at 09:53
the ++var mean increment before the assignment and the val++ means increment after that assignment. Thats why it wasn't updating for you. – Brill Pappin Feb 18 '15 at 23:54

Pavenhimself · Answer 5 · 2014-02-26T09:49:07.583

0

Have you tried debugging? Try debugging this line:

int playerScoreInc = playerScore ++;

The playerScoreInc doesn't increment.

edited Feb 26 '14 at 09:49

answered Feb 26 '14 at 09:39

Pavenhimself

527
1
5
18

Thanks for the reply, I had tired debugging but didn't get anything useful. I now have the answer. – davidbain Feb 26 '14 at 09:51
If you debugged this code line I copied, you would have been the value didn't increment. Then the you would have found the answer yourself easily :) – Pavenhimself Feb 26 '14 at 11:24

Android SQLite incrementing a column value

5 Answers5

How to increment a SQLite column value