How to update existing data with SQLite

Introduction

Once a piece of data enters a database, it is very unlikely that it remains static throughout its time spent in a table. Data is updated to reflect changes in systems they represent to remain relevant and up to date. SQLite allows you to change the values in records using the UPDATE SQL command.

UPDATE functions similar to INSERT (in that you specify columns and their desired values) and DELETE (in that you provide the criteria needed to target specific records). You are also able to modify data either one by one or in bulk. In this article, we will dive into how to use UPDATE effectively to manage your data that is already stored in tables.

Using UPDATE to modify data

The basic syntax of the UPDATE command looks something like this:

UPDATE my_table
SET
        column1 = value1,
        column2 = value2,
WHERE
        id = 1;

The basic structure involves three seperate clauses:

• specifying a table to act on
• providing the columns you wish to update as well as their new values
• defining any criteria SQLite needs to evaluate to determine which records to match

While you can assign values directly to columns like we did above, you can also use the column list syntax too, as is often seen in INSERT commands.

For instance, we can alter the above example to look as follows:

UPDATE my_table
SET (column1, column2) =
        (value1, value2)
WHERE
        id = 1;

Returning records modified by the UPDATE command

By default, SQLite does not show the number of rows impacted by an UPDATE statement. However, SQLite added the RETURNING clause modelled after PostgreSQL in version 3.35.0. This clause causes the commands to return all or part of the records that were modified.

You can use the asterisk * symbol to return all of the columns of the modified rows much like a SELECT statement:

UPDATE my_table
SET
        column1 = value1,
        column2 = value2,
WHERE
        id = 1
RETURNING *;

Additionally, you can also specify exact columns that you care about displaying with/without an alias using AS:

UPDATE my_table
SET
        column1 = value1,
        column2 = value2
WHERE
        id = 1
RETURNING column1 AS 'first column';

Updating records based on values in another table

Updating data based on new external data is a relatively streamlined process. You just need to provide the table, columns, new values, and the targeting criteria.

However, with SQLite you can also use UPDATE to conditionally update table values based on information in another table within your database. The basic syntax will look something like this:

UPDATE table1
SET table1.column1 =(
    SELECT table2.column1
    FROM table2
    WHERE table1.column2 = table2.column2);

Here, we are directly updating the value of column1 in table1 to be the return of a SELECT subquery on table2, but only in rows where column2 of table1 matches column2 of table2. The FROM clause indicates a connection between the two tables and WHERE specifies the conditions.

As an example, let's suppose that we have two tables called book and author.

CREATE TABLE author (
  id INTEGER PRIMARY KEY,
  first_name TEXT,
  last_name TEXT,
  last_publication TEXT
);

CREATE TABLE book (
    id INTEGER PRIMARY KEY,
    author_id INT REFERENCES author.id
    title TEXT,
    publication_year INTEGER
);

INSERT INTO author (first_name, last_name)
VALUES
        ('Leo', 'Tolstoy'),
        ('James', 'Joyce'),
        ('Jean-Paul', 'Sarte');

INSERT INTO book (author_id, title, publication_year)
VALUES
        (1, 'Anna Karenina', '1877'),
        (1, 'War and Peace', '1867'),
        (2, 'Ulysses', '1920'),
        (2, 'Dubliners', '1914'),
        (3, 'Nausea', '1938');

These two tables have a relation with book.author_id referencing author.id. Currently the last_publication for the author table is NULL. We can populate it with the author's latest published book in our book table using FROM and WHERE clauses to bring the two tables together.

Here, we show an example updating last_publication:

UPDATE author
SET last_publication=(
  SELECT title
  FROM book
  WHERE author_id = author.id
  ORDER BY author_id, publication_year DESC);

If you query the author table now, it will show you the title of their most recent publication in the database:

SELECT * FROM author;

+------------+------------+-----------+--------------------------+
      id       first_name  last_name      last_publication     
+-------------+------------+-----------+--------------------------+
       1       Leo          Tolstoy        Anna Karenina       
       2       James         Joyce             Ulysses         
       3       Jean-Paul     Sarte             Nausea          
+-------------+------------+-----------+--------------------------+

Conclusion

In this guide, we took a look at the basic ways that you can modify existing data within a table using the UPDATE command. Execution of these basic concepts allows you to specify the exact criteria necessary to identify the existing rows within a table, update column names with values, and optionally return the rows that were impacted with RETURNING. The UPDATE command is critical for managing your data after the initial insertion into your databases.

FAQ

UPDATE my_table
SET
        column1 = value1,
        column2 = value2,
WHERE
        id = 1;

UPDATE my_table
SET
        column1 = value1,
        column2 = value2,
WHERE
        color = 'blue';

UPDATE my_table
SET
        column1 = value1,
        column2 = value2,
WHERE
        id = 1
RETURNING *;