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.
The basic syntax of the
UPDATE command looks something like this:
UPDATE my_tableSETcolumn1 = value1,column2 = value2,WHEREid = 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
For instance, we can alter the above example to look as follows:
UPDATE my_tableSET (column1, column2) =(value1, value2)WHEREid = 1;
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
UPDATE my_tableSETcolumn1 = value1,column2 = value2,WHEREid = 1RETURNING *;
Additionally, you can also specify exact columns that you care about displaying with/without an alias using
UPDATE my_tableSETcolumn1 = value1,column2 = value2WHEREid = 1RETURNING column1 AS 'first column';
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 table1SET table1.column1 =(SELECT table2.column1FROM table2WHERE table1.column2 = table2.column2);
Here, we are directly updating the value of
table1 to be the return of a
SELECT subquery on
table2, but only in rows where
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
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.idtitle 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
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
WHERE clauses to bring the two tables together.
Here, we show an example updating
UPDATE authorSET last_publication=(SELECT titleFROM bookWHERE author_id = author.idORDER 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 Karenina2 James Joyce Ulysses3 Jean-Paul Sarte Nausea+-------------+------------+-----------+--------------------------+
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
UPDATE command is critical for managing your data after the initial insertion into your databases.