Update Statement

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.

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;

key	value
1	original value
2	original value 2

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;

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.

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

Upsert (Insert or Update)

See the Insert documentation for details.