UPDATE | PostgreSQL Tutorial

The UPDATE statement changes column values in existing rows. You target the table, declare new values with SET, and narrow which rows are affected with WHERE. Omitting WHERE updates every row in the table — always double-check before running an unfiltered update in production.

Basic UPDATE syntax

The standard form updates one or more columns for rows that match the condition:

UPDATE courses
SET published_date = '2020-08-01'
WHERE course_id = 3;

Output:

UPDATE 1

The command tag returns the count of rows updated, including rows where the value did not actually change. To update multiple columns at once, separate each assignment with a comma in the SET clause.

You can also update a column based on its current value using an expression:

-- Increase every course price by 5%
UPDATE courses
SET price = price * 1.05;

Output:

UPDATE 5

Because there is no WHERE clause, all five rows are updated.

Returning the updated rows

PostgreSQL extends standard SQL with a RETURNING clause that returns the updated row values after the change is applied — no additional SELECT required:

UPDATE courses
SET published_date = '2020-07-01'
WHERE course_id = 2
RETURNING *;

Output:

 course_id |        course_name         | price  |        description         | published_date
-----------+----------------------------+--------+----------------------------+----------------
         2 | PostgreSQL Admininstration | 349.99 | A PostgreSQL Guide for DBA | 2020-07-01
(1 row)

You can return specific columns or rename them with AS:

UPDATE courses
SET price = price * 1.10
WHERE course_id = 1
RETURNING course_id, course_name, price AS new_price;

Practical tips

Always test your WHERE clause with a SELECT first to confirm which rows will be affected before running the update.
Wrap updates in an explicit transaction when they are part of a multi-step operation — you can ROLLBACK if something goes wrong downstream.
To update rows based on data in another table, use the UPDATE ... FROM (update-join) syntax rather than a correlated subquery.
The RETURNING clause reflects values after the update, so it is safe to use for audit logging in the same statement.
UPDATE acquires a row-level lock on every row it touches — for large batch updates, consider updating in smaller chunks to avoid long lock contention.

Reference: PostgreSQL documentation — UPDATE.

Frequently Asked Questions

What does UPDATE 0 mean in PostgreSQL?

UPDATE 0 means the WHERE condition matched no rows, so nothing was changed. This is not an error — it simply indicates the filter did not find any qualifying rows.

Can I update multiple columns in one UPDATE statement?

Yes. List multiple column assignments in the SET clause separated by commas: UPDATE t SET col1 = val1, col2 = val2 WHERE condition;

How do I update rows based on values from another table?

Use the FROM clause (UPDATE join): UPDATE table1 SET col = t2.val FROM table2 t2 WHERE table1.id = t2.id; This is PostgreSQL-specific syntax. See the UPDATE Join tutorial for a full example.

Does UPDATE return the old or new values in RETURNING?

RETURNING returns the new values — after the update has been applied. If you need to capture the old value, store it in a variable or CTE before running the update.

How do I safely run a large UPDATE without locking the table?

Break the update into smaller batches using a loop or a CTE with LIMIT, committing after each batch. This releases row locks between commits and keeps the table accessible to other queries.