How to update and increment a database?

Question

I have code for a delete function which works. The columns in the db are COL_TASK_TITLE and COL_NUM . my increment function isn't working. I have also provided the delete function below which fully works.

   public void deleteTask(View view) {

        View parent = (View) view.getParent();
        TextView taskTextView = (TextView) 
        parent.findViewById(R.id.task_title);
        String task = String.valueOf(taskTextView.getText());
        SQLiteDatabase db = mHelper.getWritableDatabase();
        db.delete(TaskContract.TaskEntry.TABLE,
        TaskContract.TaskEntry.COL_TASK_TITLE + " = ?",
        new String[]{task});
        db.close();
        updateUI();
    }


    public void incrment(View view) {
        View parent = (View) view.getParent();
        TextView taskTextView = (TextView) 
        parent.findViewById(R.id.task_title);
        String task = String.valueOf(taskTextView.getText());
        SQLiteDatabase db = mHelper.getWritableDatabase();
        ContentValues cv = new ContentValues();
        cv.put("COL_NUM",COL_NUM+1)
        db.update(TaskContract.TaskEntry.TABLE,cv
            TaskContract.TaskEntry.COL_TASK_TITLE + " = ?",
            new String[]{task});
        db.close();
        updateUI();
    }

Answer 1

I guess COL_NUM is the name of the column, right?
So this line is wrong:

cv.put("COL_NUM", COL_NUM + 1);

because you think that you add a number to the value of the column where in reality you concatenate 1 to the name of the column.
What you must do is increment the current value by 1 like this:

db.execSQL(
    "update " + TaskContract.TaskEntry.TABLE + 
    " set " + COL_TaskContract.TaskEntry.COL_NUM + " = " + COL_TaskContract.TaskEntry.COL_NUM + " + 1" + 
    " where " + TaskContract.TaskEntry.COL_TASK_TITLE + " = ?", new Object[] {task});

If there is a case that COL_NUM is null then change the statement to this:

db.execSQL(
    "update " + TaskContract.TaskEntry.TABLE + 
    " set " + COL_TaskContract.TaskEntry.COL_NUM + " = coalesce(" + COL_TaskContract.TaskEntry.COL_NUM + ", 0) + 1" + 
    " where " + TaskContract.TaskEntry.COL_TASK_TITLE + " = ?", new Object[] {task});

So change your method to this:

public void incrment(View view) {
    View parent = (View) view.getParent();
    TextView taskTextView = (TextView) parent.findViewById(R.id.task_title);
    String task = taskTextView.getText().toString();
    SQLiteDatabase db = mHelper.getWritableDatabase();
    db.execSQL(
        "update " + TaskContract.TaskEntry.TABLE +
        " set " + COL_TaskContract.TaskEntry.COL_NUM + " = coalesce(" + COL_TaskContract.TaskEntry.COL_NUM + ", 0) + 1" +
        " where " + TaskContract.TaskEntry.COL_TASK_TITLE + " = ?", new Object[] {task}
    );
    db.close();
    updateUI();
}

Of course you could use the update() method like you do, but first you should find the existing value in COL_NUM, increment it, put it in cv and then execute update().