Records stored within databases are not often static. They must be updated to reflect changes in the systems they represent to remain relevant. PostgreSQL allows you to change the values in records using the
UPDATE SQL command.
In many ways,
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 can modify the data in any of the columns of a table either one at a time or in bulk. In this guide, we will explore how to use this command effectively to manage your data once it's already in tables.
The basic syntax of the
UPDATE command looks something like this:
UPDATE my_tableSETcolumn1 = value1,column2 = value2WHEREid = 1;
As shown above, 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, and
- defining any criteria PostgreSQL needs to evaluate to determine which records to match
In the basic template above, we demonstrated a style assigning values to columns directly. You can also use the column list syntax too, as is often seen in
For instance, the example above could also be specified like this:
UPDATE my_tableSET (column1, column2) =(value1, value2)WHEREid = 1;
When successfully committed, PostgreSQL confirms the action by outputting the name of the operation and the number of rows impacted:
Like many other commands, PostgreSQL allows you to append a
RETURNING clause onto the
UPDATE command. This causes the commands to return all or part of the records that were modified.
You can use the star
* symbol to return all of the columns of the modified rows:
UPDATE my_tableSETcolumn1 = value1,column2 = value2WHEREid = 1RETURNING *;
Alternatively, you can specify the exact columns you care about to display only specific attributes:
UPDATE my_tableSETcolumn1 = value1,column2 = value2WHEREid = 1RETURNING column1 AS 'first column';
Here, we also used a column alias to set the label of the column header in the output.
Updates based on providing new external data are relatively straightforward. You just need to provide the table, the columns, the new values, and the targeting criteria.
However, you can also use
UPDATE to conditionally update table values based on information stored in a joined table. The basic syntax looks like this:
UPDATE table1SET table1.column1 = <some_value>FROM table2WHERE table1.column2 = table2.column2;
Here, we are updating the value of
column1 in the
table1 table to
<some_value>, but only in rows where
FROM clause indicates a join between the two tables and
WHERE construction specifies the join conditions.
As an example, suppose that we have two tables called
CREATE TABLE director (id SERIAL PRIMARY KEY,name TEXT NOT NULL,latest_film TEXT);CREATE TABLE film (id SERIAL PRIMARY KEY,title TEXT NOT NULL,director_id INT REFERENCES director(id),release_date DATE NOT NULL);INSERT INTO director (name)VALUES('frank'),('bob'),('sue');INSERT INTO film (title, director_id, release_date)VALUES('first movie', 1, '2010-08-24'),('second movie', 1, '2010-12-15'),('third movie', 2, '2011-01-01'),('fourth movie', 2, '2012-08-02');
These two tables have a relation with
director.id. Currently, the
latest_film for the
director table is
NULL. However, we can populate it by with the director's latest film title using
WHERE clauses to bring to bring the two tables together.
Here, we use a
WITH clause to create a Common Table Expression (CTE) called
latest_films that we can reference in our
WITH latest_films AS (SELECT DISTINCT ON (director_id)*FROMfilmORDER BYdirector_id,release_date DESC)UPDATE director set latest_film = title FROM latest_filmsWHERE director.id = latest_films.director_id;
If you query the
director table, it should show you each director's latest film now:
SELECT * FROM director;
id | name | latest_film----+-------+--------------3 | sue |1 | frank | second movie2 | bob | fourth movie(3 rows)
In this guide, we've taken a look at the basic ways that you can modify existing data within a table using the
UPDATE command. Using these basic concepts, you can specify the exact criteria necessary to identify the existing rows within a table, update column names with new values, and optionally return the rows that were impacted. The
UPDATE command is essential for managing your data after its initial ingestion into your databases.