UPDATE

Go Up to Statement and Function Reference (Language Reference Guide)

Changes the data in all or part of an existing row in a table, view, or active set of a cursor. Available in gpre, DSQL, and isql.

SQL form:

UPDATE [TRANSACTION <transaction>] {table | view}
SET col = val [, col = val …]
[WHERE search_condition | WHERE CURRENT OF cursor]
[ORDER BY order_list]
[ROWS value [TO upper_value] [BY step_value][PERCENT][WITH TIES]];

DSQL and isql form:

UPDATE {table | view}
SET col = val [, col = val …]
[WHERE search_condition
[ORDER BY order_list]
[ROWS value [TO upper_value] [BY step_value][PERCENT][WITH TIES]]

val = {
col [array_dim]
| :variable
| constant
| expr
| function
| udf ([val [, val …]])
| NULL
| USER
| ?}
[COLLATE collation]
array_dim = [[x:]y [, [x:]y …]]
constant = num | 'string' | charsetname 'string'
function = CAST (val AS data_type)
| UPPER (val)
| GEN_ID (generator, val)

<expr> = A valid SQL expression that results in a single value.

<search_condition> = See CREATE TABLE. for a full description.

Notes on the UPDATE statement:

In SQL and isql, you cannot use <val> as a parameter placeholder (like “?”).
In DSQL and isql, <val> cannot be a variable.
You cannot specify a COLLATE clause for Blob columns.

Argument	Description
`TRANSACTION` <transaction>	Name of the transaction under control of which the statement is executed
<table> \| <view>	Name of an existing table or view to update.
`SET` <col> = <val>	Specifies the columns to change and the values to assign to those columns
`WHERE` <search_condition>	Searched update only; specifies the conditions a row must meet to be modified
`WHERE CURRENT` `OF` <cursor>	Positioned update only; specifies that the current row of a cursor active set is to be modified Not available in DSQL and `isql`
`ORDER BY` <order_list>	Specifies columns to order, either by column name or ordinal number in the query, and the sort order (`ASC` or `DESC`) for the returned rows
`ROWS` <value> [`TO` <upper_value>] [`BY` <step_value>] [`PERCENT`][`WITH TIES`]	<value> is the total number of rows to return if used by itself <value> is the starting row number to return if used with `TO` <value> is the percent if used with `PERCENT` <upper_value> is the last row or highest percent to return If <step_value> = <n>, returns every <n>th row, or <n> percent rows `PERCENT` causes all previous `ROWS` values to be interpreted as percents `WITH TIES` returns additional duplicate rows when the last value in the ordered sequence is the same as values in subsequent rows of the result set; must be used in conjunction with `ORDER BY`

Description: UPDATE modifies one or more existing rows in a table or view. UPDATE is one of the database privileges controlled by GRANT and REVOKE.

For searched updates, the optional WHERE clause can be used to restrict updates to a subset of rows in the table. Searched updates cannot update array slices.

Important:
Without a WHERE clause, a searched update modifies all rows in a table.

When performing a positioned update with a cursor, the WHERE CURRENT OF clause must be specified to update one row at a time in the active set.

Note:
When updating a Blob column, UPDATE replaces the entire Blob with a new value.

Examples: The following isql statement modifies a column for all rows in a table:

UPDATE CITIES
SET POPULATION = POPULATION * 1.03;

The next embedded SQL statement uses a WHERE clause to restrict column modification to a subset of rows:

EXEC SQL
UPDATE PROJECT
SET PROJ_DESC = :blob_id
WHERE PROJ_ID = :proj_id;

Advance To:

WHENEVER

UPDATE

See Also

Advance To: