UPDATE updates rows in a table.
If you update a row that contains a column referenced by a and has
an , all of the dependent rows will also be updated.
Required privileges
The user must have theSELECT and UPDATE on the table.
Synopsis
Parameters
| Parameter | Description |
|---|---|
common\_table\_expr | See . |
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 you want to perform, or the you want to use. To fill columns with their , 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. |
FROM table\_ref | Specify a table to reference, but not update, in UPDATE expressions, or in RETURNING and WHERE clauses. For more details, see Reference other tables in an update. |
select\_stmt | A . Each value must match the of its column on the left side of =. |
WHERE a\_expr | a\_expr must be a that returns Boolean values using columns (e.g., =). 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 and for more details. |
limit\_clause | A LIMIT clause. See 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 . To return nothing in the response, not even the number of rows updated, use RETURNING NOTHING. |
ONLY ... \* | Supported for compatibility with PostgreSQL table inheritance syntax. This clause is a no-op, as CockroachDB does not currently support table inheritance. |
Force index selection for updates
By using the (also known as “index hinting”), you can override CockroachDB’s index selection and use a specific for updating rows of a named table.Index selection can impact , but does not change the
result of a query.
The syntax to force an update for a specific index is:
Reference other tables in an update
To reference values from a table other than the table being updated, add aFROM clause that specifies one or more tables in the cluster. Values from tables specified in a FROM clause can be used in UPDATE expressions, and in RETURNING and WHERE clauses.
When executing an UPDATE query with a FROM clause, CockroachDB the target table (i.e., the table being updated) to the tables referenced in the FROM clause. The output of this join should have the same number of rows as the rows being updated in the target table, as CockroachDB uses a single row from the join output to update a given row in the target table. If the join produces more rows than the rows being updated in the target table, there is no way to predict which row from the join output will be used to update a row in the target table.
For an example, see Update using values from a different table.
Bulk-update data
To update a large number of rows (i.e., tens of thousands of rows or more), we recommend iteratively updating subsets of the rows that you want to update, until all of the rows have been updated. You can write a script to do this, or you can write a loop into your application. For guidance and an example, see .Examples
Setup
To follow along, run to start a temporary, in-memory cluster with the sample dataset preloaded:Update a single column in a single row
Update multiple columns in a single row
Update using SELECT statement
Update with default values
Update using values from a different table
Update all rows
If you do not use the
WHERE clause to specify the rows to be updated, the values for all rows will be updated.If the session variable is set
to
true, the client will prevent the update. sql_safe_updates is set to true by default.Update and return values
In this example, theRETURNING clause returns the id value of the row updated. The language-specific versions assume that you have installed the relevant .
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 with index hints
Suppose that you create a multi-column index on theusers table with the name and city columns.
UPDATE statement:
EXPLAIN statement shows that the optimizer scans the newly-created users_name_city_idx index when performing the update. This makes sense, as you are performing an update based on the name column.
Although users_name_city_idx is likely the most efficient index for the table scan, you may want to assess the performance difference between scanning on users_name_city_idx and scanning on the primary index. You can provide an index hint (i.e., force the index selection) to use the primary key of the users table:
Limit the size of rows
To help you avoid failures arising from misbehaving applications that bloat the size of rows, you can specify the behavior when a row or individual larger than a specified size is written to the database. Use thesql.guardrails.max_row_size_log to discover large rows and sql.guardrails.max_row_size_err to reject large rows.
When you write a row that exceeds sql.guardrails.max_row_size_log:
INSERT,UPSERT,UPDATE,CREATE TABLE AS,CREATE INDEX,ALTER TABLE,ALTER INDEX,IMPORT, orRESTOREstatements will log aLargeRowto the channel.SELECT,DELETE,TRUNCATE, andDROPare not affected.
sql.guardrails.max_row_size_err:
INSERT,UPSERT, andUPDATEstatements will fail with a code54000 (program_limit_exceeded)error.CREATE TABLE AS,CREATE INDEX,ALTER TABLE,ALTER INDEX,IMPORT, andRESTOREstatements will log aLargeRowInternalevent to the channel.SELECT,DELETE,TRUNCATE, andDROPare not affected.
sql.guardrails.max_row_size_log in conjunction with SELECT pg_column_size() queries to detect and fix any existing large rows before lowering sql.guardrails.max_row_size_err.