SQLite Go: Updating Data in a Table

Summary: in this tutorial, you will learn how to update data in a table from an SQLite database in Go.

How to update data in a table #

To update a row in a table, you follow these steps:

First, connect to the SQLite database file:

db, err := sql.Open("sqlite", "./my.db")

Second, construct an UPDATE statement that updates one or more columns in a table:

sql := "UPDATE table SET ...";

If you want to pass data to the UPDATE statement, you can use the ? placeholders. For example:

UPDATE countries
SET area = ? population = ? name = ?
WHERE id = ?

Third, call the Exec() method of the DB struct’s instance to execute the CREATE TABLE statement:

result, err = db.Exec(sql)

If the UPDATE statement includes placeholders (?), you need to pass the corresponding values in the Exec() method:

result, err = db.Exec(sql, area, population, name, id)

The result is an instance of the struct that implements the Result interface.

The Result interface includes the RowsAffected() method that returns the number of rows affected by the update statement.

Updating data in the countries table #

Step 1. Define an Update() function that updates the population of a country in the countries table:

func Update(db *sql.DB, id int, population int) (int64, error) {
    sql := `UPDATE countries SET population = ? WHERE id = ?;`
    result, err := db.Exec(sql, population, id)
    if err != nil {
        return 0, err
    }
    return result.RowsAffected()
}

How it works.

First, construct an UPDATE statement that updates data in the countries table:

sql := `UPDATE countries SET population = ? WHERE id = ?;`

Second, call the Exec() method to execute the UPDATE statement:

result, err := db.Exec(sql, population, id)

Third, return 0 and Error if an error occurs during the update:

if err != nil {
   return 0, err
}

Finally, return the number of rows updated by the UPDATE statement:

return result.RowsAffected()

Step 3. Call the Update() function in the main function to update the population of the country id 1:

package main

import (
    "database/sql"
    "fmt"

    _ "github.com/glebarez/go-sqlite"
)

func main() {
    // connect to the SQLite database
    db, err := sql.Open("sqlite", "./my.db")
    if err != nil {
        fmt.Println(err)
        return
    }
    defer db.Close()

    // Update the population of a country
    _, err = Update(db, 1, 346037975)
    if err != nil {
        fmt.Println(err)
        return
    }    
    
}

Step 4. Run the Go program

go run main.go country.go

Verifying data #

Step 1. Open your terminal and navigate to the project directory.

Step 2. Connect to the my.db using the sqlite3 shell:

sqlite3 my.db

Step 2. Format the query output:

.header on
.mode column
.nullvalue null

Step 3. Retrieve data from the countries table:

select * from countries where id = 1;

Output:

id  name           population   area
--  -------------  -----------  ---------
1   United States  346037975.0  9826675.0

The output shows that the population has updated successfully.

Step 4. Quit the sqlite3 tool:

.quit

Summary #

Call the Exec() method of the DB struct to execute an UPDATE statement to update a new row in a table.

Was this tutorial helpful?