Updating row in SQLite database creates a duplicate row

Question

I have a SQLite database in my Android app with 4 default entries. When I try to update one row in the database, it updates it properly, but it also creates a duplicate of the original row and gives it a new ID.

This database only has a single column, plus its ID column, but I have a different database with multiple columns and the update works fine for that/no duplicates, but not for this one.

The original 4 default are Work, Health, Family, and Spiritual with IDs of 1, 2, 3, and 4, respectively. After the update, it's Work 2.0, Health, Family, Spiritual, and Work, with IDs of 1, 2, 3, 4, and 5.

Here is my update method in my ContentProvider:

@Override
public int update(Uri uri, ContentValues values, String selection, String[] selectionArgs) {
    String tableName;
    int uriNum = uriMatcher.match(uri);

    switch (uriNum) {
        //Case 1 is for the entire Goals table
        case 1:
            break;

        //Case 2 is for a specific row in the Goals table
        case 2:
            if (selection != null) {
                selection = selection + "_ID = " + uri.getLastPathSegment();
            }
            else {
                selection = "_ID = " + uri.getLastPathSegment();
            }
            break;

        //Case 3 is for the entire Categories table
        case 3:
            break;

        //Case 4 is for a specific row in the Categories table
        case 4:
            if (selection != null) {
                selection = selection + "_ID = " + uri.getLastPathSegment();
            }
            else {
                selection = "_ID = " + uri.getLastPathSegment();
            }
            break;

        default:
            throw new IllegalArgumentException("URI not found.");
    }

    if(uriNum == 1 || uriNum == 2) {
        tableName = GoalsContract.Goals.TABLE_NAME;
    }
    else {
        tableName = CategoriesContract.Categories.TABLE_NAME;
    }

    db = dbHelper.getWritableDatabase();
    dbHelper.onCreate(db);

    Log.d(TAG,
            tableName + "\n" +
            selection + "\n"
    );

    int count = db.update(tableName, values, selection, selectionArgs);

    getContext().getContentResolver().notifyChange(uri, null);
    return count;
}

And the calling code:

    ContentValues updatedValues = new ContentValues();
    updatedValues.put(CategoriesContract.Categories.COLUMN_NAME_NAME, "Work 2.0");

    Uri uri = ContentUris.withAppendedId(CategoriesContract.Categories.CONTENT_URI, 1);

    int count = getContentResolver().update(uri, updatedValues, null, null);

EDIT:

The dbHelper class:

public void onCreate(SQLiteDatabase db) {
    db.execSQL(GoalsContract.Goals.SQL_CREATE_GOALS);
    db.execSQL(CategoriesContract.Categories.SQL_CREATE_CATEGORIES);

    //TODO Do on a different thread
    addDefaultData(db);
}

private void addDefaultData(SQLiteDatabase db) {
    String[] defaultCategories = {"Work", "Health", "Family", "Spiritual"};

    for(int x = 0; x < defaultCategories.length; x++) {
        ContentValues values = new ContentValues();
        values.put(CategoriesContract.Categories.COLUMN_NAME_NAME, defaultCategories[x]);
        db.insert(CategoriesContract.Categories.TABLE_NAME, null, values);
    }
}

And the actual SQL to create the table:

    static final String SQL_CREATE_CATEGORIES =
     "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " (" +
     _ID + " INTEGER PRIMARY KEY, " +
     COLUMN_NAME_NAME + " TEXT NOT NULL UNIQUE);" ;

Answer 1

Here is a sample SQLiteDatabase update method:

db.update(TABLE_NAME, ContentValues, KEY_COLUMN_NAME + " = ? ", new String[]{String.valueOf(COLUMN_VALUE)})

Modify your update() method as below:

@Override
public int update(Uri uri, ContentValues values, String selection, String[] selectionArgs) {

    String tableName;
    int uriNum = uriMatcher.match(uri);

    switch (uriNum) {
        //Case 1 is for the entire Goals table
        case 1:
            break;

        //Case 2 is for a specific row in the Goals table
        case 2:
            if (selection != null) {
                selection = selection + "_ID = ? ";
            }
            else {
                selection = "_ID = ? ";
            }
            break;

        //Case 3 is for the entire Categories table
        case 3:
            break;

        //Case 4 is for a specific row in the Categories table
        case 4:
            if (selection != null) {
                selection = selection + "_ID = ? ";
            }
            else {
                selection = "_ID = ? ";
            }
            break;

        default:
            throw new IllegalArgumentException("URI not found.");
    }

    if(uriNum == 1 || uriNum == 2) {
        tableName = GoalsContract.Goals.TABLE_NAME;
    }
    else {
        tableName = CategoriesContract.Categories.TABLE_NAME;
    }

    db = dbHelper.getWritableDatabase();
    dbHelper.onCreate(db);

    Log.d(TAG, tableName + "\n" + selection + "\n");

    int count = db.update(tableName, values, selection, selectionArgs);

    getContext().getContentResolver().notifyChange(uri, null);

    return count;
}

Call update() method as below:

ContentValues updatedValues = new ContentValues();
updatedValues.put(CategoriesContract.Categories.COLUMN_NAME_NAME, "Work 2.0");

Uri uri = ContentUris.withAppendedId(CategoriesContract.Categories.CONTENT_URI, 1);

int count = getContentResolver().update(uri, updatedValues, null, new String[]{String.valueOf(1));

Hope this will help~