Updating and deleting SQLite records in Android

Local SQLite storage is useful when an Android app needs to keep information available without a network connection. A shopping list, appointment tracker, inventory tool or café ordering app can create, read, edit and remove rows directly on the device. Android’s SQLiteDatabase API provides the core methods, while a helper class manages database creation and upgrades.

This tutorial uses Kotlin and a small tasks table as an example. The same approach applies to customer records, notes, products and other structured data. Careful validation, parameterised selection clauses and clear feedback are essential when users change stored information.

Model editable records clearly

A practical table needs a primary key that uniquely identifies each row. It should also use sensible column names and types. SQLite stores values dynamically, but declaring types still makes the schema easier to understand and maintain.

CREATE TABLE tasks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    notes TEXT,
    completed INTEGER NOT NULL DEFAULT 0
)

The id value should be used when updating or deleting a specific task. Avoid identifying rows by the title, because two tasks may have the same name. A title such as “Buy milk” could appear several times in an app used by a household in Sydney or Melbourne.

A Kotlin database helper can create this table when the application is installed:

class AppDbHelper(context: Context) :
    SQLiteOpenHelper(context, "tasks.db", null, 1) {

    override fun onCreate(db: SQLiteDatabase) {
        db.execSQL("""
            CREATE TABLE tasks (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT NOT NULL,
                notes TEXT,
                completed INTEGER NOT NULL DEFAULT 0
            )
        """.trimIndent())
    }

    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        // Add migration code when the schema changes
    }
}

Build safe update operations

An update changes selected columns in an existing row. ContentValues is the standard Android container for values passed to SQLite. The whereClause and whereArgs parameters ensure that only the intended record is changed.

fun updateTask(
    context: Context,
    taskId: Long,
    title: String,
    notes: String?,
    completed: Boolean
): Int {
    val helper = AppDbHelper(context)
    val db = helper.writableDatabase

    val values = ContentValues().apply {
        put("title", title.trim())
        put("notes", notes?.trim())
        put("completed", if (completed) 1 else 0)
    }

    val rowsChanged = db.update(
        "tasks",
        values,
        "id = ?",
        arrayOf(taskId.toString())
    )

    db.close()
    return rowsChanged
}

The returned integer tells you how many records were affected. A result of 1 usually means the edit worked, while 0 can mean that the row no longer exists. The question mark is important: values should not be concatenated into SQL strings. Parameterised selection arguments reduce errors and protect against SQL injection.

Validate data before opening the database. For example, reject a blank title, limit unexpectedly long notes and ensure that the record identifier is valid. An Australian delivery app might store suburb names such as Parramatta or Fremantle, but those values still need ordinary input validation rather than special SQL handling.

Delete rows without losing control

Deleting a record uses the same selection pattern as updating. The safest basic operation targets one primary key:

fun deleteTask(context: Context, taskId: Long): Boolean {
    val helper = AppDbHelper(context)
    val db = helper.writableDatabase

    val rowsDeleted = db.delete(
        "tasks",
        "id = ?",
        arrayOf(taskId.toString())
    )

    db.close()
    return rowsDeleted == 1
}

Never call delete("tasks", null, null) unless the intention is to remove every row. A missing WHERE clause can wipe the entire table. If an app supports bulk deletion, make that operation explicit and protect it with a confirmation step.

For related records, consider foreign keys and deletion rules. A customer may have several orders, or a project may contain many notes. Deleting the parent can either block the operation or remove dependent rows, depending on the business rule. Enable foreign keys when appropriate and test the behaviour on a fresh database and an upgraded database.

Connect database methods to the UI

An edit screen should receive the selected record’s identifier as well as its visible fields. When the user taps Save, collect the current values, validate them and call the update method. After a successful operation, return to the list screen or reload the displayed item.

A delete action should communicate that the operation is permanent. An AlertDialog can show the task title and offer Cancel and Delete buttons. This is especially useful for apps containing household expenses, work schedules or local stock records, where an accidental tap could remove information users cannot easily recreate.

After either operation, refresh the RecyclerView or ListView. One straightforward approach is to query the database again and submit the new list to a ListAdapter. For larger applications, use a repository and ViewModel, with Room or another persistence layer handling observable data. Direct SQLite remains valuable for learning and for small projects, but database work should not run on the main thread.

Handle concurrency, validation and Australian privacy

Use a background executor, coroutine or other asynchronous mechanism for writes. Opening, updating and closing a database on the UI thread can make a screen freeze, particularly on older Android phones or while an app is processing many rows. Transactions are useful when several updates must succeed together:

db.beginTransaction()
try {
    // Perform related updates or deletes here
    db.setTransactionSuccessful()
} finally {
    db.endTransaction()
}

Keep personal information to a minimum. An Australian app may store names, addresses or appointment details for people in Brisbane, Adelaide or Perth. The Privacy Act 1988 and the Australian Privacy Principles can affect how personal information is collected, retained, secured and deleted, especially when local records are synchronised with a server. A delete button should make clear whether it removes only the device copy or also cloud data.

Dates deserve attention too. SQLite does not provide a dedicated date type, so store a consistent ISO 8601 string or a Unix timestamp. This avoids confusion when users travel between Australian time zones, such as from Perth to Melbourne. Display dates in the user’s local format while keeping the stored representation consistent.

Test record changes in a real app

Test a successful update, an empty title, a missing identifier and an unchanged value. Confirm that editing one row does not alter another row with a similar title. Test deletion from both the list and detail screens, then rotate the device or recreate the activity to ensure the change remains in the database.

Database upgrades also need testing. Increase the schema version, add migration logic and install the new application over an older version containing real sample records. A careless onUpgrade() implementation can discard data, while missing columns can cause update operations to fail.

Finally, test the app with realistic Australian data and habits: long suburb names, mobile numbers, local currency values and records created while the device is offline. A small SQLite feature becomes reliable when its selection rules, user feedback, privacy behaviour and persistence across app restarts all work together.