update

Update one or more rows in a table

Syntax

update_stmt:
          UPDATE [database_name.]table_name [[AS] correlation_name] 
                 SET column_name = value_expr [, column_name = value_expr]... 
                 [WHERE {conditional_expr | CURRENT OF cursor_name}]

Description

The UPDATE statement modifies the column values in one or more rows from the specified table table_name. The statement sets the column values to the results of the specified expressions or NULL. Table columns that are referenced in the conditional_expr and in each expression can only come from table_name.

The UPDATE statement is capable of two types of updates: searched updates and positioned updates. In a searched update, the UPDATE statement modifies all rows of the table that satisfy the specified conditional expression. A positioned UPDATE is specified using the WHERE CURRENT OF cursor_name clause. The cursor_name must be that associated with an updateable SELECT statement on another statement handle in the same connection that has been compiled, executed, and fetched so that it is positioned on a valid row of its result set when the positioned UPDATE is executed. The columns that can be updated are only those that are specified in the SELECT statement's FOR UPDATE clause. If no OF column name list was specified there, then any of the SELECT statement result columns can be updated. Any columns declared in the table can be referenced in the associated UPDATE (i.e., used in the SET assignment of one of the updateable columns). The cursor name associated with the SELECT statement can be retrieved by a call to SQLGetCursorName or set by the application through a call to SQLSetCursorName in the RDM SQL/ODBC API.

If a PRIMARY or UNIQUE KEY is referenced by foreign keys, the behavior of the UPDATE statement is determined based on the ON UPDATE clause specified in the CREATE TABLE. The default action (no ON UPDATE clause specified) is to restrict (i.e. disallow) updates on a primary or unique key column in which there exists one or more rows in the referencing table with matching foreign key values. The ON UPDATE RESTRICT option explicitly specifies this same behavior. If the foreign key is declared with ON UPDATE CASCADE then the values of all matching foreign key rows will be changed to the new primary or unique key value.

Example

start trans;

update author set last_name = "BronteE" where last_name = "Bronte";

insert into author values "BronteC", "Bronte, Charlotte", "F", 1816, 1855,
    "English novelist, one of the 3 sisters whose novels are English lit. standards.";

commit;

See Also

SELECT

DELETE