On this page 
The UPDATE statement updates rows in a table.
Required Privileges
The user must have the SELECT and UPDATE privileges on the table.
Synopsis
Parameters
| Parameter | Description |
|---|---|
table_name | The name of the table that contains the rows you want to update. |
AS name | An alias for the table name. When an alias is provided, it completely hides the actual table name. |
column_name | The name of the column whose values you want to update. |
a_expr | The new value you want to use, the aggregate function you want to perform, or the value expression you want to use. |
DEFAULT | To fill columns with their default values, use DEFAULT VALUES in place of a_expr. To fill a specific column with its default value, leave the value out of the a_expr or use DEFAULT at the appropriate position. |
column_name_list | A comma-separated list of column names, in parentheses. |
select_with_parens | A comma-separated list of values or value expressions, in parentheses. To update values of multiple rows, use a comma-separated list of parentheses. Each value must match the data type of its column. Also, if column names are listed ( qualified_name_list), values must be in corresponding order; otherwise, they must follow the declared order of the columns in the table. |
WHERE a_expr | a_expr must be an expression that returns Boolean values using columns (e.g., <column> = <value>). Update rows that return TRUE.Without a WHERE clause in your statement, UPDATE updates all rows in the table. |
RETURNING target_list | Return values based on rows updated, where target_list can be specific column names from the table, * for all columns, or a computation on specific columns. To return nothing in the response, not even the number of rows updated, use RETURNING NOTHING. |
Examples
Update a Single Column in a Single Row
> SELECT * FROM accounts; +----+----------+----------+ | id | balance | customer | +----+----------+----------+ | 1 | 10000.50 | Ilya | | 2 | 4000.0 | Julian | | 3 | 8700.0 | Dario | | 4 | 3400.0 | Nitin | +----+----------+----------+ (4 rows) > UPDATE accounts SET balance = 5000.0 WHERE id = 2; > SELECT * FROM accounts; +----+----------+----------+ | id | balance | customer | +----+----------+----------+ | 1 | 10000.50 | Ilya | | 2 | 5000.0 | Julian | | 3 | 8700.0 | Dario | | 4 | 3400.0 | Nitin | +----+----------+----------+ (4 rows) Update Multiple Columns in a Single Row
> UPDATE accounts SET (balance, customer) = (9000.0, 'Kelly') WHERE id = 2; > SELECT * FROM accounts; +----+----------+----------+ | id | balance | customer | +----+----------+----------+ | 1 | 10000.50 | Ilya | | 2 | 9000.0 | Kelly | | 3 | 8700.0 | Dario | | 4 | 3400.0 | Nitin | +----+----------+----------+ (4 rows) > UPDATE accounts SET balance = 6300.0, customer = 'Stanley' WHERE id = 3; > SELECT * FROM accounts; +----+----------+----------+ | id | balance | customer | +----+----------+----------+ | 1 | 10000.50 | Ilya | | 2 | 9000.0 | Kelly | | 3 | 6300.0 | Stanley | | 4 | 3400.0 | Nitin | +----+----------+----------+ (4 rows) Update Using SELECT Statement
> UPDATE accounts SET (balance, customer) = (SELECT balance, customer FROM accounts WHERE id = 2) WHERE id = 4; > SELECT * FROM accounts; +----+----------+----------+ | id | balance | customer | +----+----------+----------+ | 1 | 10000.50 | Ilya | | 2 | 9000.0 | Kelly | | 3 | 6300.0 | Stanley | | 4 | 9000.0 | Kelly | +----+----------+----------+ (4 rows) Update with Default Values
> UPDATE accounts SET balance = DEFAULT where customer = 'Stanley'; > SELECT * FROM accounts; +----+----------+----------+ | id | balance | customer | +----+----------+----------+ | 1 | 10000.50 | Ilya | | 2 | 9000.0 | Kelly | | 3 | NULL | Stanley | | 4 | 9000.0 | Kelly | +----+----------+----------+ (4 rows) Update All Rows
Warning:
If you do not use the WHERE clause to specify the rows to be updated, the values for all rows will be updated.> UPDATE accounts SET balance = 5000.0; > SELECT * FROM accounts; +----+---------+----------+ | id | balance | customer | +----+---------+----------+ | 1 | 5000.0 | Ilya | | 2 | 5000.0 | Kelly | | 3 | 5000.0 | Stanley | | 4 | 5000.0 | Kelly | +----+---------+----------+ (4 rows) Update and Return Values
In this example, the RETURNING clause returns the id value of the row updated. The language-specific versions assume that you have installed the relevant client drivers.
Tip:
This use of RETURNING mirrors the behavior of MySQL's last_insert_id() function.Note:
When a driver provides a query() method for statements that return results and an exec() method for statements that do not (e.g., Go), it's likely necessary to use the query() method for UPDATE statements with RETURNING.> UPDATE accounts SET balance = DEFAULT WHERE id = 1 RETURNING id; +----+ | id | +----+ | 1 | +----+ (1 row) # Import the driver. import psycopg2 # Connect to the "bank" database. conn = psycopg2.connect( database='bank', user='root', host='localhost', port=26257 ) # Make each statement commit immediately. conn.set_session(autocommit=True) # Open a cursor to perform database operations. cur = conn.cursor() # Update a row in the "accounts" table # and return the "id" value. cur.execute( 'UPDATE accounts SET balance = DEFAULT WHERE id = 1 RETURNING id' ) # Print out the returned value. rows = cur.fetchall() print('ID:') for row in rows: print([str(cell) for cell in row]) # Close the database connection. cur.close() conn.close() The printed value would look like:
ID: ['1'] # Import the driver. require 'pg' # Connect to the "bank" database. conn = PG.connect( user: 'root', dbname: 'bank', host: 'localhost', port: 26257 ) # Update a row in the "accounts" table # and return the "id" value. conn.exec( 'UPDATE accounts SET balance = DEFAULT WHERE id = 1 RETURNING id' ) do |res| # Print out the returned value. puts "ID:" res.each do |row| puts row end end # Close communication with the database. conn.close() The printed value would look like:
ID: {"id"=>"1"} package main import ( "database/sql" "fmt" "log" _ "github.com/lib/pq" ) func main() { //Connect to the "bank" database. db, err := sql.Open( "postgres", "postgresql://root@localhost:26257/bank?sslmode=disable" ) if err != nil { log.Fatal("error connecting to the database: ", err) } // Update a row in the "accounts" table // and return the "id" value. rows, err := db.Query( "UPDATE accounts SET balance = DEFAULT WHERE id = 1 RETURNING id", ) if err != nil { log.Fatal(err) } // Print out the returned value. defer rows.Close() fmt.Println("ID:") for rows.Next() { var id int if err := rows.Scan(&id); err != nil { log.Fatal(err) } fmt.Printf("%d\n", id) } } The printed value would look like:
ID: 1 var async = require('async'); // Require the driver. var pg = require('pg'); // Connect to the "bank" database. var config = { user: 'root', host: 'localhost', database: 'bank', port: 26257 }; pg.connect(config, function (err, client, done) { // Closes communication with the database and exits. var finish = function () { done(); process.exit(); }; if (err) { console.error('could not connect to cockroachdb', err); finish(); } async.waterfall([ function (next) { // Update a row in the "accounts" table // and return the "id" value. client.query( `UPDATE accounts SET balance = DEFAULT WHERE id = 1 RETURNING id`, next ); } ], function (err, results) { if (err) { console.error('error updating and selecting from accounts', err); finish(); } // Print out the returned value. console.log('ID:'); results.rows.forEach(function (row) { console.log(row); }); finish(); }); }); The printed value would like:
ID: { id: '1' }