From InterBase

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 ]])
| ?}
[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


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>]

  • <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.

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.

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:


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

SET PROJ_DESC = :blob_id
WHERE PROJ_ID = :proj_id;

See Also

Advance To: