I have a database with table Cities, which has two columns id and is_default declared as integer. I would like to update all cities the way that only one city has value 1 set in is_default column at the same time. My database is wrapped by ContentProvider so I want to use ContentResolver for this. Here's my code:
ContentValues contentValues = new ContentValues();
contentValues.put(DatabaseHelper.COLUMN_DEFAULT, "CASE WHEN " + DatabaseHelper.COLUMN_ID + " != " + id + " THEN 0 ELSE 1 END");
getContentResolver().update(BenefitsProvider.CITY_CONTENT_URI, contentValues, null, null);
Where id is an id of a city, which I would like to make the default one. The issue is that this query doesn't change the is_default column value at all. I'm sure that ContentProvider code is fine and it works properly with all other update cases.
Is it possible to have only one city at the same time with is_default column value being 1 using this approach? Is there any other way I can achieve this?