Swift + SQLite: Update Data (UPDATE)

In this article, we will explain how to update data in a SQLite table using Swift.

We will add code to update data in SQLite to the example we created in Swift - Insert Data into SQLite (INSERT).

Defining a Function to Update Data in the Students Table

Previously, we inserted data into the students table, which stores Student struct data as follows:

struct Student {
    var StudentID: Int
    var StudentNumber: String
    var FirstName: String
    var LastName: String
    var Age: Int?
    
    init(studentID: Int, studentNumber: String, firstName: String, lastName: String, age: Int?) {
        self.StudentID = studentID
        self.StudentNumber = studentNumber
        self.FirstName = firstName
        self.LastName = lastName
        self.Age = age
    }
}

CREATE TABLE IF NOT EXISTS students (
    student_id INTEGER NOT NULL PRIMARY KEY,
    student_number TEXT NOT NULL,
    first_name TEXT NULL,
    last_name TEXT NULL,
    age INTEGER NULL
);

This time, we will create an updateStudent function that takes a Student object and updates data in the students table.

Open DBService.swift and add the following code:

func updateStudent(student: Student) -> Bool {    
    let updateSql = """
    UPDATE  students
    SET     student_number = ?,
            first_name = ?,
            last_name = ?,
            age = ?
    WHERE   student_id = ?
    """
    var updateStmt: OpaquePointer? = nil
    
    if sqlite3_prepare_v2(db, (updateSql as NSString).utf8String, -1, &updateStmt, nil) != SQLITE_OK {
        print("db error: \(getDBErrorMessage(db))")
        return false
    }
    
    sqlite3_bind_text(updateStmt, 1, (student.StudentNumber as NSString).utf8String, -1, nil)
    sqlite3_bind_text(updateStmt, 2, (student.FirstName as NSString).utf8String, -1, nil)
    sqlite3_bind_text(updateStmt, 3, (student.LastName as NSString).utf8String, -1, nil)
    
    if student.Age == nil {
        sqlite3_bind_null(updateStmt, 4)
    } else {
        sqlite3_bind_int(updateStmt, 4, Int32(student.Age!))
    }
    
    sqlite3_bind_int(updateStmt, 5, Int32(student.StudentID))
    
    if sqlite3_step(updateStmt) != SQLITE_DONE {
        print("db error: \(getDBErrorMessage(db))")
        sqlite3_finalize(updateStmt)
        return false
    }
    sqlite3_finalize(updateStmt)
    return true
}

This assumes that a record with the same StudentID already exists in the students table.

The updateStudent function works as follows:

Lines 2 - 9 define the SQLite UPDATE statement as updateSql.

SQLite statements must be compiled into a bytecode program before execution using a preparation function. Here we use sqlite3_prepare_v2() to compile the SQL statement.

int sqlite3_prepare_v2(
  sqlite3 *db,            /* Database handle */
  const char *zSql,       /* SQL statement, UTF-8 encoded */
  int nByte,              /* Maximum length of zSql in bytes. */
  sqlite3_stmt **ppStmt,  /* OUT: Statement handle */
  const char **pzTail     /* OUT: Pointer to unused portion of zSql */
);

Line 10 defines an OpaquePointer? variable updateStmt for the statement handle. Line 12 calls sqlite3_prepare_v2() with the database handle, SQL statement, and &updateStmt.

If successful, sqlite3_prepare_v2() returns SQLITE_OK; otherwise, it returns an error code. On failure, getDBErrorMessage() prints the error message and returns false.

Lines 17 - 27 bind values to the prepared statement.

To bind a string, use sqlite3_bind_text(); to bind an integer, use sqlite3_bind_int(). The function you use depends on the data type.

Line 17 uses sqlite3_bind_text() to bind the first ? placeholder in the updateSql statement with student.StudentNumber, converted to UTF-8.

Similarly, lines 18 and 19 use sqlite3_bind_text() to bind the second and third ? placeholders with student.FirstName and student.LastName.

Lines 21 - 25 handle the fourth ?. If student.Age is nil, sqlite3_bind_null() is used to bind NULL. If student.Age is not nil, sqlite3_bind_int() is used to bind student.Age as an integer.

Line 27 uses sqlite3_bind_int() to bind the fifth ? placeholder with student.StudentID, converted to Int32.

Lines 29 - 33 use the sqlite3_step() function to evaluate and execute the compiled SQL statement.

If the execution succeeds, sqlite3_step() returns SQLITE_DONE. If it fails, the getDBErrorMessage() function is used to print the error message.

Before returning, the sqlite3_finalize() function is called to destroy the prepared statement object.

Finally, lines 34 - 35 call sqlite3_finalize() again and then return true, confirming that the statement executed successfully.

Updating Data in the Students Table

Now, let's use the updateStudent() function to update data. We will update the data we inserted previously in Swift - Insert Data into SQLite (INSERT).

Modify the code in ViewController.swift as follows:

var student1 = Student(studentID: 1, studentNumber: "S000001", firstName: "Yuta", lastName: "Tanaka", age: 16)
        
//        if DBService.shared.insertStudent(student: student1) {
//            print("Insert success")
//        } else {
//            print("Insert Failed")
//        }

student1.LastName = "Yamada"
student1.Age = 17

if DBService.shared.updateStudent(student: student1) {
    print("Update success")
} else {
    print("Update Failed")
}

Lines 9 - 10 update the LastName and Age values of student1.

Lines 12 - 16 call DBService.shared.updateStudent() to update the data.

When executed successfully, the output console will display:

Opened connection to database
Update success

You can confirm the changes by opening the generated SQLite file in DB Browser for SQLite. (See How to Locate SQLite Files for details.)

The data in the students table has been updated:

That's it — we have explained how to update data in a SQLite table using Swift.

Next, we will retrieve data from SQLite using Swift.

Next Article: Swift - Select Data from SQLite (SELECT)