PostgreSQL UPDATE Statement

In this article, we will discuss how to work with the UPDATE statement.

In PostgreSQL, UPDATE statements allow you to update existing rows in a table.

PostgreSQL UPDATE syntax

The PostgreSQL UPDATE statement can update one or more rows in a table, and can update the value of one or more columns in a table. The following is the basic syntax of the UPDATE statement:

UPDATE [IGNORE] table_name
SET
    column_name1 = value1,
    column_name2 = value2,
    ...
[WHERE clause]
[RETURNING expr];

Explanation:

  • Specify the name of the table to update data after the UPDATE keyword.

  • Use the SET clause to set the new value for the column. Multiple columns are separated by commas. Column values ​​can be ordinary literals, expression operations, or subqueries.

  • Use the WHERE clause to specify conditions for rows to update. Only rows that match the WHERE condition will be updated.

  • The WHERE clause is optional. If no WHERE clause specified, all rows in the table are updated.

  • The RETURNING clause is optional. It is used to return the updated rows.

    The expr can be column names or an expressions. Use commas to separate multiple columns or expressions. You can also use to * represent all columns in a table.

    If no RETURNING clause specified, the UPDATE statement returns the number of updated rows.

For a UPDATE statement, the WHERE clause is very important. Do not omit the WHERE clause.

PostgreSQL UPDATE Examples

Below we will use a few examples to demonstrate the specific usage of UPDATE.

We will use the tables in the Sakila sample database for demonstration, please install the Sakila sample database in PostgreSQL first.

In the following example, the customer’s information is stored in the customer table.

Use UPDATE to modify a single column value

In this example, we’ll modify the email of the customer with customer_id 1 to [email protected].

  1. Use the following SELECT statement to view the row before updating.

    SELECT first_name, last_name, email
FROM customer
WHERE customer_id = 1;

     first_name | last_name |             email
------------+-----------+-------------------------------
 MARY       | SMITH     | [email protected]

  2. Use the following UPDATE statement to update the value of the email column.

    UPDATE customer
SET email = '[email protected]'
WHERE customer_id = 1;

    UPDATE 1

    In this UPDATE statement:

    • The condition (customer_id = 1) for the update is specified via the WHERE clause.
    • Set the value of the email column to a new email via the SET clause.

    The UPDATE statement returned UPDATE 1, that means that one row was updated.

  3. Alternatively, you can view the updated row directly using the following RETURNING clause:

    UPDATE customer
SET email = '[email protected]'
WHERE customer_id = 1
RETURNING first_name, last_name, email;

     first_name | last_name |               email
------------+-----------+-----------------------------------
 MARY       | SMITH     | [email protected]

Use UPDATE to modify multiple column values

In this example, we will simultaneously update the first_name, last_name, and email columns for the customer with customer_id 1.

UPDATE customer
SET first_name = 'Tim',
    last_name = 'Duncan',
    email = '[email protected]'
WHERE customer_id = 1
RETURNING first_name, last_name, email;

 first_name | last_name |             email
------------+-----------+-------------------------------
 Tim        | Duncan    | [email protected]

UPDATE and expression

In UPDATE statement, the value of the column can be set to an expression, such as a function or other operation.

To update the domain name portion of all customer emails, use the following UPDATE statement:

UPDATE customer
SET email = REPLACE(email, 'sakilacustomer.org', 'sqliz.com')
RETURNING first_name, last_name, email;

 first_name  |  last_name   |              email
-------------+--------------+---------------------------------
 PATRICIA    | JOHNSON      | [email protected]
 LINDA       | WILLIAMS     | [email protected]
 BARBARA     | JONES        | [email protected]
 ELIZABETH   | BROWN        | [email protected]
 JENNIFER    | DAVIS        | [email protected]
 MARIA       | MILLER       | [email protected]
 SUSAN       | WILSON       | [email protected]
 MARGARET    | MOORE        | [email protected]
 DOROTHY     | TAYLOR       | [email protected]
 LISA        | ANDERSON     | [email protected]
...
(599 rows)

Note that, there is not a WHERE clause in the statement, so all rows in the table is updated.

UPDATE and subquery

The following example shows how to bind a random store for a customer that does not bind any store.

UPDATE customer
SET store_id = (
    SELECT store_id
    FROM store
    ORDER BY random()
    LIMIT 1
  )
WHERE store_id IS NULL;

In this example, we return a random store id with the following subquery:

SELECT store_id
FROM store
ORDER BY random()
LIMIT 1

In the SET clause, set the value of store_id to the result of the subquery above.

Conclusion

PostgreSQL UPDATE statement is used to update one or more existing rows in a table. If the UPDATE statement has a RETURNING clause, it returns the updated rows, otherwise it returns the number of updated rows.