# How to update existing data with SQLite

> Part of Prisma's Data Guide. The complete index is at https://www.prisma.io/dataguide/llms.txt.

Learn how to update existing data using SQLite.

Canonical URL: https://www.prisma.io/dataguide/sqlite/update-data
Authors: Alex Emerich
Content revision: 9203918a9aaf69cc77281e7d729cf18a601fb802f3b8a7c2f9065c3ab94733f5
Source revision: 41ccbc7d5038f7b4391787911e24ad936598fab2
Last updated: 2026-10-01

## 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

In the following templates, replace `my_table`, column names, `value1`, and `value2` with your table, columns, and SQL values. The basic syntax of `UPDATE` is:

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

The basic structure involves three separate 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:

```sql pseudocode
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](https://www.prisma.io/dataguide/intro/database-glossary#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:

```sql pseudocode
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`:

```sql pseudocode
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:

```sql pseudocode
UPDATE table1
SET 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`, using the row where `column2` of `table1` matches `column2` of `table2`. If no row matches, the subquery returns `NULL`. Keep the matching column unique so the result is unambiguous. 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`.

**Expand to see the commands to create and populate these tables**

```sql
PRAGMA foreign_keys = ON;

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`. SQLite enforces that reference only while foreign keys are turned on for the connection, and the `sqlite3` shell starts with them off, so the setup commands begin with `PRAGMA foreign_keys = ON;`. 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`:

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

If you query the `author` table now, it will show you the title of their most recent publication in the database. In the `sqlite3` shell, `.mode table` prints the result as a table:

```sql
.mode table
SELECT * FROM author;
```

```text output
+----+------------+-----------+------------------+
| 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

**How do you edit records in SQLite?**

You can edit records in SQLite by using an `UPDATE` statement.

The basic syntax would look like:

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

**How do you perform a bulk update in SQLite?**

In SQLite, you can use the `UPDATE` command looking something like the following:

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

This syntax allows you to update in bulk depending on the `WHERE` criteria. In this case the columns are updated for any record where the `color` column is `'blue'`.

**How do you return records modified by the `UPDATE` command in SQLite?**

By default, SQLite does not show the number of rows impacted by an `UPDATE` statement.

However, SQLite does have the `RETURNING` clause to return all or part of the records that were modified.

The basic syntax looks like this with `*` specifying to return all column names:

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

**How do you insert or update in SQLite depending on whether data exists?**

In SQLite, there is not an `IF EXISTS` clause like many other relational databases.

To control an `INSERT` or `UPDATE` for data existing, you will want to add an [`ON CONFLICT` clause to your statement](https://sqlite.org/lang_conflict.html).

**How do you update with join in SQLite?**

To perform an `UPDATE` with a join in SQLite, you can use the [`UPDATE FROM` extension](https://www.sqlite.org/lang_update.html#upfrom). This extension to SQL allows an `UPDATE` statement to be driven by other tables in the database.

With `UPDATE-FROM`, you can join the target table against other tables in the database in order to help compute which rows need updating and what new values should be on those rows.