UPDATE statement
Use the UPDATE statement to change the values in one or more columns of one or more existing rows in a table or view.
Syntax
| Element | Description | Restrictions | Syntax |
|---|---|---|---|
| alias | Temporary name that you declare here for a local or remote table | The AS keyword must precede alias if SET is the identifier of alias | Identifier |
| condition | Logical criteria that updated rows must satisfy | Cannot be a UDR nor a correlated subquery | Condition |
| cursor | Name of a cursor whose current row is to be updated | Cannot be a host variable. You cannot update a row that includes aggregates | Identifier |
| synonym, table, view | Synonym, table, or view that contains rows to be updated | The synonym and the table or view to which it points must exist | Database Object Name |
Usage
- A row in a table: a single row, a group of rows, or all rows in a table
- An element in a column of a collection data type
- In a column of a named or unnamed ROW data type, a field or all fields.
With Informix®, you can also use this statement to change the values in one or more elements in Informix® ESQL/C or SPL collection variables or ROW variables.
For information on how to update elements of a collection variable, see Collection-Derived Table. Sections that follow in this description of the UPDATE statement describe how to update a row in a table.
You must either own the table or have the Update privilege for the table; see GRANT statement. To update data in a view, you must have the Update privilege, and the view must meet the requirements that are explained in Updating Rows Through a View.
The target of the UPDATE statement cannot be a table object that the CREATE EXTERNAL TABLE statement defined.
The cursor (as defined in the SELECT ... FOR UPDATE portion of a DECLARE statement) can contain only column names. If you omit the WHERE clause, all rows of the target table are updated.
If you are using effective checking and the checking mode is set to IMMEDIATE, all enabled constraints are checked at the end of each UPDATE statement. If the checking mode is set to DEFERRED, all enabled constraints are not checked until the transaction is committed.
In DB-Access, if you omit the WHERE clause and are in interactive mode, DB-Access does not run the UPDATE statement until you confirm that you want to change all rows. If the statement is in a command file, however, and you are running at the command line, the statement executes immediately.
Example
CREATE VIEW cust_view AS SELECT * FROM customer;
UPDATE cust_view SET customer_num=10001 WHERE customer_num=101;