UPDATE

The UPDATE statement updates rows in a table.

Warning:

If you update a row that contains a column referenced by a foreign key constraint and has an ON UPDATE action, all of the dependent rows will also be updated.

Required privileges

The user must have the SELECT and UPDATE privileges on the table.

Synopsis

WITHcommon_table_expr,UPDATEtable_nameAStable_alias_nameSETcolumn_name=a_expr(column_name,)=(select_stmta_expr,),WHEREa_exprsort_clauselimit_clauseRETURNINGtarget_listNOTHING

Parameters

ParameterDescription
common_table_exprSee Common Table Expressions.
table_nameThe name of the table that contains the rows you want to update.
AS table_alias_nameAn alias for the table name. When an alias is provided, it completely hides the actual table name.
column_nameThe name of the column whose values you want to update.
a_exprThe new value you want to use, the aggregate function you want to perform, or the scalar expression you want to use.
DEFAULTTo 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_nameThe name of a column to update.
select_stmtA selection query. Each value must match the data type of its column on the left side of =.
WHERE a_expra_expr must be a scalar 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.
sort_clauseAn ORDER BY clause. See Ordering Query Results for more details.
limit_clauseA LIMIT clause. See Limiting Query Results for more details.
RETURNING target_listReturn values based on rows updated, where target_list can be specific column names from the table, * for all columns, or computations using scalar expressions. 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

  1. > SELECT * FROM accounts;
  1. +----+----------+----------+
  2. | id | balance  | customer |
  3. +----+----------+----------+
  4. |  1 | 10000.50 | Ilya     |
  5. |  2 |   4000.0 | Julian   |
  6. |  3 |   8700.0 | Dario    |
  7. |  4 |   3400.0 | Nitin    |
  8. +----+----------+----------+
  9. (4 rows)
  1. > UPDATE accounts SET balance = 5000.0 WHERE id = 2;
  1. > SELECT * FROM accounts;
  1. +----+----------+----------+
  2. | id | balance  | customer |
  3. +----+----------+----------+
  4. |  1 | 10000.50 | Ilya     |
  5. |  2 |   5000.0 | Julian   |
  6. |  3 |   8700.0 | Dario    |
  7. |  4 |   3400.0 | Nitin    |
  8. +----+----------+----------+
  9. (4 rows)

Update multiple columns in a single row

  1. > UPDATE accounts SET (balance, customer) = (9000.0, 'Kelly') WHERE id = 2;
  1. > SELECT * FROM accounts;
  1. +----+----------+----------+
  2. | id | balance  | customer |
  3. +----+----------+----------+
  4. |  1 | 10000.50 | Ilya     |
  5. |  2 |   9000.0 | Kelly    |
  6. |  3 |   8700.0 | Dario    |
  7. |  4 |   3400.0 | Nitin    |
  8. +----+----------+----------+
  9. (4 rows)
  1. > UPDATE accounts SET balance = 6300.0, customer = 'Stanley' WHERE id = 3;
  1. > SELECT * FROM accounts;
  1. +----+----------+----------+
  2. | id | balance  | customer |
  3. +----+----------+----------+
  4. |  1 | 10000.50 | Ilya     |
  5. |  2 |   9000.0 | Kelly    |
  6. |  3 |   6300.0 | Stanley  |
  7. |  4 |   3400.0 | Nitin    |
  8. +----+----------+----------+
  9. (4 rows)

Update using SELECT statement

  1. > UPDATE accounts SET (balance, customer) =
  2.     (SELECT balance, customer FROM accounts WHERE id = 2)
  3.      WHERE id = 4;
  1. > SELECT * FROM accounts;
  1. +----+----------+----------+
  2. | id | balance  | customer |
  3. +----+----------+----------+
  4. |  1 | 10000.50 | Ilya     |
  5. |  2 |   9000.0 | Kelly    |
  6. |  3 |   6300.0 | Stanley  |
  7. |  4 |   9000.0 | Kelly    |
  8. +----+----------+----------+
  9. (4 rows)

Update with default values

  1. > UPDATE accounts SET balance = DEFAULT where customer = 'Stanley';
  1. > SELECT * FROM accounts;
  1. +----+----------+----------+
  2. | id | balance  | customer |
  3. +----+----------+----------+
  4. |  1 | 10000.50 | Ilya     |
  5. |  2 |   9000.0 | Kelly    |
  6. |  3 | NULL     | Stanley  |
  7. |  4 |   9000.0 | Kelly    |
  8. +----+----------+----------+
  9. (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.

  1. > UPDATE accounts SET balance = 5000.0;
  1. > SELECT * FROM accounts;
  1. +----+---------+----------+
  2. | id | balance | customer |
  3. +----+---------+----------+
  4. |  1 |  5000.0 | Ilya     |
  5. |  2 |  5000.0 | Kelly    |
  6. |  3 |  5000.0 | Stanley  |
  7. |  4 |  5000.0 | Kelly    |
  8. +----+---------+----------+
  9. (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.

  1. > UPDATE accounts SET balance = DEFAULT WHERE id = 1 RETURNING id;
  1. +----+
  2. | id |
  3. +----+
  4. |  1 |
  5. +----+
  6. (1 row)
  1. # Import the driver.
  2. import psycopg2
  4. # Connect to the "bank" database.
  5. conn = psycopg2.connect(
  6.     database='bank',
  7.     user='root',
  8.     host='localhost',
  9.     port=26257
  10. )
  12. # Make each statement commit immediately.
  13. conn.set_session(autocommit=True)
  15. # Open a cursor to perform database operations.
  16. cur = conn.cursor()
  18. # Update a row in the "accounts" table
  19. # and return the "id" value.
  20. cur.execute(
  21.     'UPDATE accounts SET balance = DEFAULT WHERE id = 1 RETURNING id'
  22. )
  24. # Print out the returned value.
  25. rows = cur.fetchall()
  26. print('ID:')
  27. for row in rows:
  28.     print([str(cell) for cell in row])
  30. # Close the database connection.
  31. cur.close()
  32. conn.close()

The printed value would look like:

  1. ID:
  2. ['1']
  1. # Import the driver.
  2. require 'pg'
  4. # Connect to the "bank" database.
  5. conn = PG.connect(
  6.     user: 'root',
  7.     dbname: 'bank',
  8.     host: 'localhost',
  9.     port: 26257
  10. )
  12. # Update a row in the "accounts" table
  13. # and return the "id" value.
  14. conn.exec(
  15.     'UPDATE accounts SET balance = DEFAULT WHERE id = 1 RETURNING id'
  16. ) do |res|
  18. # Print out the returned value.
  19. puts "ID:"
  20.     res.each do |row|
  21.         puts row
  22.     end
  23. end
  25. # Close communication with the database.
  26. conn.close()

The printed value would look like:

  1. ID:
  2. {"id"=>"1"}
  1. package main
  3. import (
  4.         "database/sql"
  5.         "fmt"
  6.         "log"
  8.         _ "github.com/lib/pq"
  9. )
  11. func main() {
  12.         //Connect to the "bank" database.
  13.         db, err := sql.Open(
  14.                 "postgres",
  15.                 "postgresql://root@localhost:26257/bank?sslmode=disable"
  16.         )
  17.         if err != nil {
  18.                 log.Fatal("error connecting to the database: ", err)
  19.         }
  21.         // Update a row in the "accounts" table
  22.         // and return the "id" value.
  23.         rows, err := db.Query(
  24.                 "UPDATE accounts SET balance = DEFAULT WHERE id = 1 RETURNING id",
  25.         )
  26.         if err != nil {
  27.                 log.Fatal(err)
  28.         }
  30.         // Print out the returned value.
  31.         defer rows.Close()
  32.         fmt.Println("ID:")
  33.         for rows.Next() {
  34.                 var id int
  35.                 if err := rows.Scan(&id); err != nil {
  36.                         log.Fatal(err)
  37.                 }
  38.                 fmt.Printf("%d\n", id)
  39.         }
  40. }

The printed value would look like:

  1. ID:
  2. 1
  1. var async = require('async');
  3. // Require the driver.
  4. var pg = require('pg');
  6. // Connect to the "bank" database.
  7. var config = {
  8.   user: 'root',
  9.   host: 'localhost',
  10.   database: 'bank',
  11.   port: 26257
  12. };
  14. pg.connect(config, function (err, client, done) {
  15.   // Closes communication with the database and exits.
  16.   var finish = function () {
  17.     done();
  18.     process.exit();
  19.   };
  21.   if (err) {
  22.     console.error('could not connect to cockroachdb', err);
  23.     finish();
  24.   }
  25.   async.waterfall([
  26.     function (next) {
  27.       // Update a row in the "accounts" table
  28.       // and return the "id" value.
  29.       client.query(
  30.         `UPDATE accounts SET balance = DEFAULT WHERE id = 1 RETURNING id`,
  31.         next
  32.       );
  33.     }
  34.   ],
  35.   function (err, results) {
  36.     if (err) {
  37.       console.error('error updating and selecting from accounts', err);
  38.       finish();
  39.     }
  40.     // Print out the returned value.
  41.     console.log('ID:');
  42.     results.rows.forEach(function (row) {
  43.       console.log(row);
  44.     });
  46.     finish();
  47.   });
  48. });

The printed value would like:

  1. ID:
  2. { id: '1' }

See also

Was this page helpful?
YesNo