# How to update existing data in PostgreSQL > Part of Prisma's Data Guide. The complete index is at https://www.prisma.io/dataguide/llms.txt. The `UPDATE` command is the primary method of altering existing data within PostgreSQL. This guide demonstrates how to use the `UPDATE` operation to modify the values within your tables. Canonical URL: https://www.prisma.io/dataguide/postgresql/inserting-and-modifying-data/updating-existing-data Authors: Justin Ellingwood Content revision: 9607eba7863fd2457b443a020cc46fc14e4dd6ad8d0c5b91f0175c4e26f009dc Source revision: 966174084131f9cfdc77eefcb4db38af72c1a29d ## 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: ```sql UPDATE my_table SET column1 = value1, column2 = value2 WHERE id = 1; ``` As shown above, the basic structure involves three separate clauses: - specifying a [table](https://www.prisma.io/dataguide/intro/database-glossary#table) to act on, - providing the [columns](https://www.prisma.io/dataguide/intro/database-glossary#column) 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: ```sql 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 ``` ## 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: ```sql 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: ```sql 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: ```sql UPDATE table1 SET table1.column1 = FROM table2 WHERE table1.column2 = table2.column2; ``` Here, we are updating the value of `column1` in the `table1` table to ``, 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`. **Expand to see the commands to create and populate these tables** ```sql 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: ```sql 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: ```sql 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.