The UPDATE statement modifies the values of rows in a table.

Examples

For every row where i is NULL, set the value to 0 instead:

UPDATE tbl
SET i = 0
WHERE i IS NULL;

Set all values of i to 1 and all values of j to 2:

UPDATE tbl
SET i = 1, j = 2;

Syntax

UPDATE changes the values of the specified columns in all rows that satisfy the condition. Only the columns to be modified need be mentioned in the SET clause; columns not explicitly modified retain their previous values.

RETURNING Clause

The RETURNING clause returns the values of the rows that were updated. It uses the same syntax as the SELECT clause, except the DISTINCT modifier is not supported. The returned values reflect the state of the rows after the update.

CREATE TABLE tbl (i INTEGER, j INTEGER);
INSERT INTO tbl VALUES (0, 1), (1, 2);
UPDATE tbl SET j = j + 10 WHERE i = 1 RETURNING *;

Update from Other Table

A table can be updated based upon values from another table. This can be done by specifying a table in a FROM clause, or using a sub-select statement. Both approaches have the benefit of completing the UPDATE operation in bulk for increased performance.

CREATE OR REPLACE TABLE original AS
    SELECT 1 AS key, 'original value' AS value
    UNION ALL
    SELECT 2 AS key, 'original value 2' AS value;

CREATE OR REPLACE TABLE new AS
    SELECT 1 AS key, 'new value' AS value
    UNION ALL
    SELECT 2 AS key, 'new value 2' AS value;

SELECT *
FROM original;

UPDATE original
    SET value = new.value
    FROM new
    WHERE original.key = new.key;

Or:

UPDATE original
    SET value = (
        SELECT
            new.value
        FROM new
        WHERE original.key = new.key
    );

SELECT *
FROM original;

Multiple Matching Source Rows

If a target row matches multiple rows in the FROM clause, the target row is updated only once, using the values of one of the matching source rows. Which source row is used is not specified. For example, the following query sets v to either 10 or 20:

CREATE TABLE target (id INTEGER, v INTEGER);
INSERT INTO target VALUES (1, 0);
CREATE TABLE source (id INTEGER, v INTEGER);
INSERT INTO source VALUES (1, 10), (1, 20);

UPDATE target
    SET v = source.v
    FROM source
    WHERE target.id = source.id;

To get deterministic results, ensure that each target row matches at most one source row, e.g., by deduplicating the source. In contrast, the sub-select form throws an error if the subquery returns more than one row (unless the scalar_subquery_error_on_multiple_rows setting is set to false), and MERGE INTO throws an error if a target row matches more than one source row.

Update from Same Table

The only difference between this case and the above is that a different table alias must be specified on both the target table and the source table. In this example AS true_original and AS new are both required.

UPDATE original AS true_original
    SET value = (
        SELECT
            new.value || ' a change!' AS value
        FROM original AS new
        WHERE true_original.key = new.key
    );

Update Using Joins

To select the rows to update, UPDATE statements can use the FROM clause and express joins via the WHERE clause. For example:

CREATE TABLE city (name VARCHAR, revenue BIGINT, country_code VARCHAR);
CREATE TABLE country (code VARCHAR, name VARCHAR);
INSERT INTO city VALUES ('Paris', 700, 'FR'), ('Lyon', 200, 'FR'), ('Brussels', 400, 'BE');
INSERT INTO country VALUES ('FR', 'France'), ('BE', 'Belgium');

To increase the revenue of all cities in France, join the city and the country tables, and filter on the latter:

UPDATE city
SET revenue = revenue + 100
FROM country
WHERE city.country_code = country.code
  AND country.name = 'France';

SELECT *
FROM city;

Upsert (Insert or Update)

See the Insert documentation for details.