This is my migration change:

Basically rename and then update some values based on the row id where isNext = 1.

I suppose I need to use a Cursor as shown in

The post-date query results in :-

Here's an SQLFiddle Demo

You code could then be :-

// For Auto-Migration
@RenameColumn(tableName = "Notifications", fromColumnName = "isNext", toColumnName = "wasShown")
static class MyAutoMigration implements AutoMigrationSpec {
    @Override
    public void onPostMigrate(@NonNull SupportSQLiteDatabase database) {
        // Invoked once auto migration is done
        database.execSQL("WITH isNext(id) AS (SELECT id FROM Notifications WHERE wasShown = 1 LIMIT 1) UPDATE Notifications SET wasShown = CASE WHEN id < (SELECT id FROM isNext) THEN 1 ELSE 0 END;");

    }
}