INFO

  • UPDATE cannot modify data in the primary key column of a table.
  • Call FLUSH after UPDATE to persist the changes to storage. This ensures that the changes are committed and visible for subsequent reads.

​
Syntax

UPDATE table_name
    SET { col_name = value, ... | ( col_name, ... ) = ( value, ... ) }
    [ WHERE condition ]
    [ RETURNING col_name ];

​
Parameters

Parameter or clauseDescription
table_nameThe table whose rows you want to update.
SET col_name = valueAssign a value or result of an expression to a specific column.col_name cannot be a primary key.
WHERE conditionSpecify which rows you want to update using an expression that returns a boolean value. Rows for which this expression returns true will be updated. If you omit the WHERE clause, all rows in the table will be updated. Subqueries are supported in the condition expression.
RETURNINGReturns the values of any column based on each updated row.

​
Example

The taxi_trips table has three records:

SELECT * FROM taxi_trips;

 id | distance |    city
----+----------+-------------
  1 |       16 | Yerba Buena
  2 |       23 | New York
  3 |        6 | Chicago
(3 rows)

The following statement updates the city name from ‘Yerba Buena’ to ‘San Francisco’. Also, it returns the value of id for the updated rows.

UPDATE taxi_trips
SET city = 'San Francisco'
WHERE city = 'Yerba Buena'
RETURNING id;

The following statement converts the distance unit from kilometer to mile for certain areas.

UPDATE taxi_trips
SET distance = distance * 0.6214
WHERE city NOT IN (SELECT city FROM restricted_zones);

Was this page helpful?

START TRANSACTIONOverview