How to update existing data in PostgreSQL

Introduction

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.

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;

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 INSERT commands.

For instance, the example above could also be specified like this:

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

When successfully committed, PostgreSQL confirms the action by outputting the name of the operation and the number of rows impacted:

UPDATE <count>

Returning records modified by the UPDATE command

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_table
SET
    column1 = value1,
    column2 = value2
WHERE
    id = 1
RETURNING *;

Alternatively, you can specify the exact columns you care about to display only specific attributes:

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

Here, we also used a column alias to set the label of the column header in the output.

Updating records based on values in another table

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 table1
SET table1.column1 = <some_value>
FROM table2
WHERE table1.column2 = table2.column2;

Here, we are updating the value of column1 in the table1 table to <some_value>, but only in rows where column2 of table1 match column2 of table2. The 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 film and director.

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 film.director_id referencing 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 FROM and 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 UPDATE statement:

WITH latest_films AS (
    SELECT DISTINCT ON (director_id)
        *
    FROM
        film
    ORDER BY
        director_id,
        release_date DESC)
UPDATE director set latest_film = title FROM latest_films
WHERE 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 movie
  2 | bob   | fourth movie
(3 rows)

Conclusion

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.