UPDATE - Amazon Redshift

UPDATE

Updates values in one or more table columns when a condition is satisfied.

Note

The maximum size for a single SQL statement is 16 MB.

Syntax

[ WITH [RECURSIVE] common_table_expression [, common_table_expression , ...] ] UPDATE table_name [ [ AS ] alias ] SET column = { expression | DEFAULT } [,...] [ FROM fromlist ] [ WHERE condition ]

Parameters

WITH clause

Optional clause that specifies one or more common-table-expressions. See WITH clause.

table_name

A temporary or persistent table. Only the owner of the table or a user with UPDATE privilege on the table may update rows. If you use the FROM clause or select from tables in an expression or condition, you must have SELECT privilege on those tables. You can't give the table an alias here; however, you can specify an alias in the FROM clause.

Note

Amazon Redshift Spectrum external tables are read-only. You can't UPDATE an external table.

alias

Temporary alternative name for a target table. Aliases are optional. The AS keyword is always optional.

SET column =

One or more columns that you want to modify. Columns that aren't listed retain their current values. Do not include the table name in the specification of a target column. For example, UPDATE tab SET tab.col = 1 is invalid.

expression

An expression that defines the new value for the specified column.

DEFAULT

Updates the column with the default value that was assigned to the column in the CREATE TABLE statement.

FROM tablelist

You can update a table by referencing information in other tables. List these other tables in the FROM clause or use a subquery as part of the WHERE condition. Tables listed in the FROM clause can have aliases. If you need to include the target table of the UPDATE statement in the list, use an alias.

WHERE condition

Optional clause that restricts updates to rows that match a condition. When the condition returns true, the specified SET columns are updated. The condition can be a simple predicate on a column or a condition based on the result of a subquery.

You can name any table in the subquery, including the target table for the UPDATE.

Usage notes

After updating a large number of rows in a table:

  • Vacuum the table to reclaim storage space and re-sort rows.

  • Analyze the table to update statistics for the query planner.

Left, right, and full outer joins aren't supported in the FROM clause of an UPDATE statement; they return the following error:

ERROR: Target table must be part of an equijoin predicate

If you need to specify an outer join, use a subquery in the WHERE clause of the UPDATE statement.

If your UPDATE statement requires a self-join to the target table, you need to specify the join condition as well as the WHERE clause criteria that qualify rows for the update operation. In general, when the target table is joined to itself or another table, a best practice is to use a subquery that clearly separates the join conditions from the criteria that qualify rows for updates.

UPDATE queries with multiple matches per row throws an error when the configuration parameter error_on_nondeterministic_update is set to true. For more information, see error_on_nondeterministic_update.

You can update a GENERATED BY DEFAULT AS IDENTITY column. Columns defined as GENERATED BY DEFAULT AS IDENTITY can be updated with values you supply. For more information, see GENERATED BY DEFAULT AS IDENTITY.