UPDATE expression

The UPDATE expression updates the specified rows in the table and writes new values into them. The condition for selecting the rows to be updated can be specified through the WHERE expression.

The number of updated rows is returned as the result of the query.

Instead of deleting or modifying rows in existing tables (expressions DELETE and UPDATE), it is recommended to create new tables based on existing tables with the necessary filters (expression CREATE TABLE) and truncate old ones if necessary (expression TRUNCATE).

Syntax

UPDATE <table_name>
    SET <col_name> = <value> [ , <col_name> = <value> , ... ]
    [ WHERE <condition> ]

Parameters

  • [ SET <col_name> = <value> ]

    Specifies the column to update and the values to be written. You can specify multiple expressions via , for any number of columns.

  • [ WHERE <condition> ]

    Specifies the condition to be used to select rows to update.

    Allowed operators inside the <condition> condition:

    • = equals

    • > greater than

    • < less than

    • in (…​) is included in the list

    • their combinations

Examples

Example 1

Create a table with numbers from 1 to 10 and update the numbers column by writing into it the values from the table itself multiplied by 100:

CREATE TABLE demo.numbers (numbers BIGINT);

INSERT INTO demo.numbers (numbers)
    SELECT unnest(generate_series(1,10));

UPDATE demo.numbers
    SET numbers = numbers*100;
+-------+
| Count |
+-------+
| 10    |
+-------+

Let’s check the contents of the table:

SELECT * FROM demo.numbers;
+---------+
| numbers |
+---------+
| 100     |
+---------+
| 200     |
+---------+
| 300     |
+---------+
| 400     |
+---------+
| 500     |
+---------+
| 600     |
+---------+
| 700     |
+---------+
| 800     |
+---------+
| 900     |
+---------+
| 1000    |
+---------+

Example 2

Create a table with two columns num_1 and num_2. Let’s write numbers from 1 to 10 into the num_1 column, and leave the num_2 column empty (there will be NULL values there). Let’s update columns num_1 and num_2: write into num_1 the values from it multiplied by 100, and into num_2 the values from num_1 multiplied by -1. Let’s do all this with the condition that the values from num_1 are in the list (2,4,6,8,10,12):

CREATE TABLE demo.numbers (num_1 BIGINT, num_2 BIGINT);

INSERT INTO demo.numbers (num_1)
    SELECT unnest(generate_series(1,10));

UPDATE demo.numbers
    SET num_1 = num_1*100, num_2 = num_1*-1
    WHERE num_1 in (2,4,6,8,10,12);
+-------+
| Count |
+-------+
| 5     |
+-------+

Let’s check the contents of the table:

SELECT * FROM demo.numbers;
+-------+-------+
| num_1 | num_2 |
+-------+-------+
| 200   | -2    |
+-------+-------+
| 400   | -4    |
+-------+-------+
| 600   | -6    |
+-------+-------+
| 800   | -8    |
+-------+-------+
| 1000  | -10   |
+-------+-------+
| 1     | null  |
+-------+-------+
| 3     | null  |
+-------+-------+
| 5     | null  |
+-------+-------+
| 7     | null  |
+-------+-------+
| 9     | null  |
+-------+-------+