Search code examples
javaandroidsqlsqlite

How can I conditionally decrement an INT value?

My table has columns Screen, Icon, and Rank. I want a better way to find rows WHERE Screen = "myScreen" AND Rank > 5 and make Rank one less than their current value.

I am doing this in Java from an SQLite manager class:

public void DeleteScreenIcon (int id, String screenName, int rank){
    int screenID = getScreenID(screenName);
    SQLiteDatabase db=this.getWritableDatabase();
    db.delete(isLookUp, colScreenID + "=" + screenID + " and " +colIconID+ "="+id, null);
    // HERE IS WHERE I NOW WANT TO DO THAT..
    db.execSQL("update "+ isLookUp +" set "+colRank+ "=" +colRank+ " -1 "+" where " + colScreenID +  "='"  +screenName + "' and " + colRank +">" +rank);
    db.close();
}
Solution

  • This update should do the trick:

    UPDATE myTable 
SET Rank = Rank - 1
WHERE Screen = "myScreen"
AND Rank > 5;