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

Parameters

Parameter Description
common_table_expr See Common Table Expressions.
table_name The name of the table that contains the rows you want to update.
AS table_alias_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 scalar 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 The name of a column to update.
select_stmt A selection query. Each value must match the data type of its column on the left side of =.
WHERE a_expr a_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_clause An ORDER BY clause. See Ordering Query Results for more details.
limit_clause A LIMIT clause. See Limiting Query Results for more details.
RETURNING target_list Return 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

Setup

The following examples use MovR, a fictional vehicle-sharing application, to demonstrate CockroachDB SQL statements. For more information about the MovR example application and dataset, see MovR: A Global Vehicle-sharing App.

To follow along, run cockroach demo to start a temporary, in-memory cluster with the movr dataset preloaded:

copy
icon/buttons/copy
$ cockroach demo

Update a single column in a single row

copy
icon/buttons/copy
> SELECT * FROM users LIMIT 10;
                   id                  |   city    |        name        |            address             | credit_card
+--------------------------------------+-----------+--------------------+--------------------------------+-------------+
  c28f5c28-f5c2-4000-8000-000000000026 | amsterdam | Maria Weber        | 14729 Karen Radial             | 5844236997
  c7ae147a-e147-4000-8000-000000000027 | amsterdam | Tina Miller        | 97521 Mark Extensions          | 8880478663
  cccccccc-cccc-4000-8000-000000000028 | amsterdam | Taylor Cunningham  | 89214 Jennifer Well            | 5130593761
  d1eb851e-b851-4800-8000-000000000029 | amsterdam | Kimberly Alexander | 48474 Alfred Hollow            | 4059628542
  19999999-9999-4a00-8000-000000000005 | boston    | Nicole Mcmahon     | 11540 Patton Extensions        | 0303726947
  1eb851eb-851e-4800-8000-000000000006 | boston    | Brian Campbell     | 92025 Yang Village             | 9016427332
  23d70a3d-70a3-4800-8000-000000000007 | boston    | Carl Mcguire       | 60124 Palmer Mews Apt. 49      | 4566257702
  28f5c28f-5c28-4600-8000-000000000008 | boston    | Jennifer Sanders   | 19121 Padilla Brooks Apt. 12   | 1350968125
  80000000-0000-4000-8000-000000000019 | chicago   | Matthew Clay       | 49220 Lisa Junctions           | 9132291015
  851eb851-eb85-4000-8000-00000000001a | chicago   | Samantha Coffey    | 6423 Jessica Underpass Apt. 87 | 9437219051
(10 rows)
copy
icon/buttons/copy
> UPDATE users SET address = '201 E Randolph St' WHERE id = '851eb851-eb85-4000-8000-00000000001a';
copy
icon/buttons/copy
> SELECT * FROM users LIMIT 10;
                   id                  |   city    |        name        |           address            | credit_card
+--------------------------------------+-----------+--------------------+------------------------------+-------------+
  c28f5c28-f5c2-4000-8000-000000000026 | amsterdam | Maria Weber        | 14729 Karen Radial           | 5844236997
  c7ae147a-e147-4000-8000-000000000027 | amsterdam | Tina Miller        | 97521 Mark Extensions        | 8880478663
  cccccccc-cccc-4000-8000-000000000028 | amsterdam | Taylor Cunningham  | 89214 Jennifer Well          | 5130593761
  d1eb851e-b851-4800-8000-000000000029 | amsterdam | Kimberly Alexander | 48474 Alfred Hollow          | 4059628542
  19999999-9999-4a00-8000-000000000005 | boston    | Nicole Mcmahon     | 11540 Patton Extensions      | 0303726947
  1eb851eb-851e-4800-8000-000000000006 | boston    | Brian Campbell     | 92025 Yang Village           | 9016427332
  23d70a3d-70a3-4800-8000-000000000007 | boston    | Carl Mcguire       | 60124 Palmer Mews Apt. 49    | 4566257702
  28f5c28f-5c28-4600-8000-000000000008 | boston    | Jennifer Sanders   | 19121 Padilla Brooks Apt. 12 | 1350968125
  80000000-0000-4000-8000-000000000019 | chicago   | Matthew Clay       | 49220 Lisa Junctions         | 9132291015
  851eb851-eb85-4000-8000-00000000001a | chicago   | Samantha Coffey    | 201 E Randolph St            | 9437219051
(10 rows)

Update multiple columns in a single row

copy
icon/buttons/copy
> UPDATE rides SET (end_address, revenue) = ('201 E Randolph St', 25.00) WHERE id = '851eb851-eb85-4000-8000-000000000104';
copy
icon/buttons/copy
> SELECT * FROM rides WHERE rider_id = '851eb851-eb85-4000-8000-00000000001a';
                   id                  |  city   | vehicle_city |               rider_id               |              vehicle_id              |         start_address         |         end_address         |        start_time         |         end_time          | revenue
+--------------------------------------+---------+--------------+--------------------------------------+--------------------------------------+-------------------------------+-----------------------------+---------------------------+---------------------------+---------+
  849ba5e3-53f7-4000-8000-000000000103 | chicago | chicago      | 851eb851-eb85-4000-8000-00000000001a | 88888888-8888-4800-8000-000000000008 | 77630 Steven Road Suite 60    | 74140 Andrew Spur           | 2018-12-30 03:04:05+00:00 | 2018-12-31 08:04:05+00:00 |   20.00
  851eb851-eb85-4000-8000-000000000104 | chicago | chicago      | 851eb851-eb85-4000-8000-00000000001a | 88888888-8888-4800-8000-000000000008 | 76707 Timothy Square          | 201 E Randolph St           | 2018-12-15 03:04:05+00:00 | 2018-12-17 07:04:05+00:00 |   25.00
  86a7ef9d-b22d-4000-8000-000000000107 | chicago | chicago      | 851eb851-eb85-4000-8000-00000000001a | 88888888-8888-4800-8000-000000000008 | 28532 Kevin Villages Suite 90 | 27493 Ortega Radial Apt. 60 | 2018-12-08 03:04:05+00:00 | 2018-12-09 03:04:05+00:00 |   36.00
  92f1a9fb-e76c-4800-8000-00000000011f | chicago | chicago      | 851eb851-eb85-4000-8000-00000000001a | 88888888-8888-4800-8000-000000000008 | 56955 Edward Walks            | 53193 Jerry Village         | 2019-01-01 03:04:05+00:00 | 2019-01-01 15:04:05+00:00 |   35.00
  94fdf3b6-45a1-4800-8000-000000000123 | chicago | chicago      | 851eb851-eb85-4000-8000-00000000001a | 88888888-8888-4800-8000-000000000008 | 63820 Robinson Fields         | 89245 Eric Orchard          | 2018-12-14 03:04:05+00:00 | 2018-12-16 10:04:05+00:00 |   80.00
(5 rows)
copy
icon/buttons/copy
> UPDATE rides SET end_address = '10000 W OHare Ave', revenue = 60.00 WHERE id = '94fdf3b6-45a1-4800-8000-000000000123';
copy
icon/buttons/copy
> SELECT * FROM rides WHERE rider_id = '851eb851-eb85-4000-8000-00000000001a';
                   id                  |  city   | vehicle_city |               rider_id               |              vehicle_id              |         start_address         |         end_address         |        start_time         |         end_time          | revenue
+--------------------------------------+---------+--------------+--------------------------------------+--------------------------------------+-------------------------------+-----------------------------+---------------------------+---------------------------+---------+
  849ba5e3-53f7-4000-8000-000000000103 | chicago | chicago      | 851eb851-eb85-4000-8000-00000000001a | 88888888-8888-4800-8000-000000000008 | 77630 Steven Road Suite 60    | 74140 Andrew Spur           | 2018-12-30 03:04:05+00:00 | 2018-12-31 08:04:05+00:00 |   20.00
  851eb851-eb85-4000-8000-000000000104 | chicago | chicago      | 851eb851-eb85-4000-8000-00000000001a | 88888888-8888-4800-8000-000000000008 | 76707 Timothy Square          | 201 E Randolph St           | 2018-12-15 03:04:05+00:00 | 2018-12-17 07:04:05+00:00 |   25.00
  86a7ef9d-b22d-4000-8000-000000000107 | chicago | chicago      | 851eb851-eb85-4000-8000-00000000001a | 88888888-8888-4800-8000-000000000008 | 28532 Kevin Villages Suite 90 | 27493 Ortega Radial Apt. 60 | 2018-12-08 03:04:05+00:00 | 2018-12-09 03:04:05+00:00 |   36.00
  92f1a9fb-e76c-4800-8000-00000000011f | chicago | chicago      | 851eb851-eb85-4000-8000-00000000001a | 88888888-8888-4800-8000-000000000008 | 56955 Edward Walks            | 53193 Jerry Village         | 2019-01-01 03:04:05+00:00 | 2019-01-01 15:04:05+00:00 |   35.00
  94fdf3b6-45a1-4800-8000-000000000123 | chicago | chicago      | 851eb851-eb85-4000-8000-00000000001a | 88888888-8888-4800-8000-000000000008 | 63820 Robinson Fields         | 10000 W OHare Ave           | 2018-12-14 03:04:05+00:00 | 2018-12-16 10:04:05+00:00 |   60.00
(5 rows)

Update using SELECT statement

copy
icon/buttons/copy
> UPDATE rides SET (revenue, start_address) =
    (SELECT revenue, end_address FROM rides WHERE id = '94fdf3b6-45a1-4800-8000-000000000123')
     WHERE id = '851eb851-eb85-4000-8000-000000000104';
copy
icon/buttons/copy
> SELECT * FROM rides WHERE rider_id = '851eb851-eb85-4000-8000-00000000001a';
                   id                  |  city   | vehicle_city |               rider_id               |              vehicle_id              |         start_address         |         end_address         |        start_time         |         end_time          | revenue
+--------------------------------------+---------+--------------+--------------------------------------+--------------------------------------+-------------------------------+-----------------------------+---------------------------+---------------------------+---------+
  849ba5e3-53f7-4000-8000-000000000103 | chicago | chicago      | 851eb851-eb85-4000-8000-00000000001a | 88888888-8888-4800-8000-000000000008 | 77630 Steven Road Suite 60    | 74140 Andrew Spur           | 2018-12-30 03:04:05+00:00 | 2018-12-31 08:04:05+00:00 |   20.00
  851eb851-eb85-4000-8000-000000000104 | chicago | chicago      | 851eb851-eb85-4000-8000-00000000001a | 88888888-8888-4800-8000-000000000008 | 10000 W OHare Ave             | 201 E Randolph St           | 2018-12-15 03:04:05+00:00 | 2018-12-17 07:04:05+00:00 |   60.00
  86a7ef9d-b22d-4000-8000-000000000107 | chicago | chicago      | 851eb851-eb85-4000-8000-00000000001a | 88888888-8888-4800-8000-000000000008 | 28532 Kevin Villages Suite 90 | 27493 Ortega Radial Apt. 60 | 2018-12-08 03:04:05+00:00 | 2018-12-09 03:04:05+00:00 |   36.00
  92f1a9fb-e76c-4800-8000-00000000011f | chicago | chicago      | 851eb851-eb85-4000-8000-00000000001a | 88888888-8888-4800-8000-000000000008 | 56955 Edward Walks            | 53193 Jerry Village         | 2019-01-01 03:04:05+00:00 | 2019-01-01 15:04:05+00:00 |   35.00
  94fdf3b6-45a1-4800-8000-000000000123 | chicago | chicago      | 851eb851-eb85-4000-8000-00000000001a | 88888888-8888-4800-8000-000000000008 | 63820 Robinson Fields         | 10000 W OHare Ave           | 2018-12-14 03:04:05+00:00 | 2018-12-16 10:04:05+00:00 |   60.00
(5 rows)

Update with default values

copy
icon/buttons/copy
> UPDATE users SET address = DEFAULT WHERE id = '19999999-9999-4a00-8000-000000000005';
copy
icon/buttons/copy
> SELECT * FROM users LIMIT 5;
                   id                  |   city    |        name        |        address        | credit_card
+--------------------------------------+-----------+--------------------+-----------------------+-------------+
  c28f5c28-f5c2-4000-8000-000000000026 | amsterdam | Maria Weber        | 14729 Karen Radial    | 5844236997
  c7ae147a-e147-4000-8000-000000000027 | amsterdam | Tina Miller        | 97521 Mark Extensions | 8880478663
  cccccccc-cccc-4000-8000-000000000028 | amsterdam | Taylor Cunningham  | 89214 Jennifer Well   | 5130593761
  d1eb851e-b851-4800-8000-000000000029 | amsterdam | Kimberly Alexander | 48474 Alfred Hollow   | 4059628542
  19999999-9999-4a00-8000-000000000005 | boston    | Nicole Mcmahon     | NULL                  | 0303726947
(5 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.

Note:
If the sql_safe_updates session variable is set to true, the client will prevent the update. sql_safe_updates is set to true by default.

copy
icon/buttons/copy
> UPDATE rides SET revenue = 7.00;
pq: rejected: UPDATE without WHERE clause (sql_safe_updates = true)

You can use a SET statement to set session variables.

copy
icon/buttons/copy
> SET sql_safe_updates = false;
copy
icon/buttons/copy
> UPDATE rides SET revenue = 7.00;
copy
icon/buttons/copy
> SELECT * FROM rides LIMIT 5;
                   id                  |   city    | vehicle_city |               rider_id               |              vehicle_id              |         start_address          |            end_address            |        start_time         |         end_time          | revenue
+--------------------------------------+-----------+--------------+--------------------------------------+--------------------------------------+--------------------------------+-----------------------------------+---------------------------+---------------------------+---------+
  c0000000-0000-4000-8000-000000000177 | amsterdam | amsterdam    | c28f5c28-f5c2-4000-8000-000000000026 | cccccccc-cccc-4000-8000-00000000000c | 65738 Williams Summit          | 72424 Thomas Field Suite 82       | 2018-12-31 03:04:05+00:00 | 2019-01-01 03:04:05+00:00 |    7.00
  c083126e-978d-4000-8000-000000000178 | amsterdam | amsterdam    | cccccccc-cccc-4000-8000-000000000028 | cccccccc-cccc-4000-8000-00000000000c | 53613 Johnson Terrace          | 12667 Monica Hollow               | 2018-12-16 03:04:05+00:00 | 2018-12-17 15:04:05+00:00 |    7.00
  c10624dd-2f1a-4000-8000-000000000179 | amsterdam | amsterdam    | c7ae147a-e147-4000-8000-000000000027 | cccccccc-cccc-4000-8000-00000000000c | 61921 Brittany Orchard Apt. 85 | 81157 Stephanie Court Suite 96    | 2018-12-30 03:04:05+00:00 | 2019-01-01 07:04:05+00:00 |    7.00
  c189374b-c6a7-4000-8000-00000000017a | amsterdam | amsterdam    | cccccccc-cccc-4000-8000-000000000028 | cccccccc-cccc-4000-8000-00000000000c | 75456 Gray View                | 69175 Christopher Shoals Suite 47 | 2018-12-23 03:04:05+00:00 | 2018-12-23 03:04:05+00:00 |    7.00
  c20c49ba-5e35-4000-8000-00000000017b | amsterdam | amsterdam    | cccccccc-cccc-4000-8000-000000000028 | cccccccc-cccc-4000-8000-00000000000c | 38892 Joseph Summit Suite 86   | 89582 Melissa Streets             | 2018-12-27 03:04:05+00:00 | 2018-12-28 18:04:05+00:00 |    7.00
(5 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.

copy
icon/buttons/copy
> UPDATE vehicles SET status = 'available' WHERE city = 'new york' RETURNING id;
                   id
+--------------------------------------+
  00000000-0000-4000-8000-000000000000
  11111111-1111-4100-8000-000000000001
(2 rows)

copy
icon/buttons/copy
# Import the driver.
import psycopg2

# Connect to the "bank" database.
conn = psycopg2.connect(
    database='movr',
    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 "vehicles" table
# and return the "id" value.
cur.execute(
    "UPDATE vehicles SET status = 'available' WHERE city = 'new york' RETURNING id;"
)

# Print out the returned value.
rows = cur.fetchall()
print('IDs:')
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:

IDs:
['00000000-0000-4000-8000-000000000000']
['11111111-1111-4100-8000-000000000001']

copy
icon/buttons/copy
# Import the driver.
require 'pg'

# Connect to the "bank" database.
conn = PG.connect(
    user: 'root',
    dbname: 'movr',
    host: 'localhost',
    port: 26257
)

# Update a row in the "vehicles" table
# and return the "id" value.
conn.exec(
    "UPDATE vehicles SET status = 'available' WHERE city = 'new york' RETURNING id;"
) do |res|

# Print out the returned value.
puts "IDs:"
    res.each do |row|
        puts row
    end
end

# Close communication with the database.
conn.close()

The printed value would look like:

IDs:
{"id"=>"00000000-0000-4000-8000-000000000000"}
{"id"=>"11111111-1111-4100-8000-000000000001"}

copy
icon/buttons/copy
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/movr?sslmode=disable",
    )
    if err != nil {
        log.Fatal("error connecting to the database: ", err)
    }

    // Update a row in the "vehicles" table
    // and return the "id" value.
    rows, err := db.Query(
        "UPDATE vehicles SET status = 'available' WHERE city = 'new york' RETURNING id;",
    )
    if err != nil {
        log.Fatal(err)
    }

    // Print out the returned value.
    defer rows.Close()
    fmt.Println("IDs:")
    for rows.Next() {
        var id string
        if err := rows.Scan(&id); err != nil {
            log.Fatal(err)
        }
        fmt.Printf("%s\n", id)
    }
}

The printed value would look like:

IDs:
00000000-0000-4000-8000-000000000000
11111111-1111-4100-8000-000000000001

copy
icon/buttons/copy
var async = require('async')
var pg = require('pg')

// Config to connect to the "movr" database.
var config = {
    user: 'root',
    host: 'localhost',
    database: 'movr',
    port: 26257
  }

// Create pool
var pool = new pg.Pool(config)

pool.connect(function (err, client, done) {

    // Close communication with the database and exit.
    var finish = function () {
        done()
        process.exit()
    }

    if (err) {
        console.error('could not connect to cockroachdb', err);
        finish()
    }
    async.waterfall([function (next) {
        client.query(
            `UPDATE vehicles SET status = 'available' WHERE city = 'new york' RETURNING id;`,
          next
        )
      }
    ],
    function (err, results) {
      if (err) {
        console.error('error updating and selecting from users', err);
        finish()
      }
      // Print out the returned value.
      console.log('IDs:')
      results.rows.forEach(function (row) {
        console.log(row)
      })

      finish()
    })
  })

The printed value would like:

IDs:
{ id: '00000000-0000-4000-8000-000000000000' }
{ id: '11111111-1111-4100-8000-000000000001' }

See also



Yes No