UPDATE

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:

SQL

UPDATE tblSET i = 0WHERE i IS NULL;

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

SQL

UPDATE tblSET i = 1, j = 2;

Syntax

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.

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.

SQL

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;

 key | value
-----+------------------
   1 | original value
   2 | original value 2

SQL

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

Or:

SQL

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

SQL

SELECT *FROM original;

 key | value
-----+-------------
   1 | new value
   2 | new value 2

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.

SQL

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:

SQL

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');

A row that the join matches more than once is updated once, with the values of one of its matches; which match is not defined. It is counted and returned by RETURNING once.

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

SQL

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

SQL

SELECT *FROM city;

 name     | revenue | country_code
----------+---------+--------------
 Paris    |     800 | FR
 Lyon     |     300 | FR
 Brussels |     400 | BE

Upsert (Insert or Update)

See the Insert documentation for details.