SQL-J Language Reference
Page 56 of 121

UPDATE statement

An UPDATE statement sets the value in a column.

You can update the current row of an open, updatable cursor. If there is no current row, or if the current row no longer satisfies the cursor's query, an exception is raised.

Syntax

{
    UPDATE TableName
        SET ColumnName = Value
        [ , ColumnName = Value} ]*
        [ WHERE clause] |
    UPDATE [ TableName]
        SET ColumnName = Value
        [ , ColumnName = Value ]*
         WHERE CURRENT OF clause
}

Value

Expression | DEFAULT

The first syntactical form is called a searched update. The second syntactical form is called a positioned update.

For searched updates, you update all rows of the table for which the WHERE clause evaluates to TRUE.

For positioned updates, you can update only columns that were included in the FOR UPDATE clause of the SELECT statement that created the cursor. If the SELECT statement did not include a FOR UPDATE clause, the columns must be in the target table of the SELECT statement.

Specifying DEFAULT for the update value sets the value of the column to the default defined for that table.

UPDATE STATEMENT Examples

UPDATE Airlines
SET basic_rate = basic_rate * 1.2
WHERE Airline = 'US'

UPDATE HOTELS
SET hotel_name = ?
WHERE hotel_id = ?

stmt.executeUpdate("UPDATE Hotels SET basic_rate = "
    "basic_rate * 1.2 " +
    "WHERE CURRENT OF " + ResultSet.getCursorName());

UPDATE HotelAvailability
SET rooms_taken = DEFAULT
WHERE hotel_id = 30

Dependency System

A searched update statement depends on the table being updated, all of its conglomerates (units of storage such as heaps or indexes), all of its constraints, and any other table named in the WHERE clause or SET expressions. A CREATE or DROP INDEX statement or an ALTER TABLE statement for the target table of a prepared searched update statement invalidates the prepared searched update statement.

The positioned update statement depends on the cursor and any tables the cursor references. You can compile a positioned update even if the cursor hasn't been opened yet. However, removing the open cursor with the JDBC close method invalidates the positioned update.

A CREATE or DROP INDEX statement or an ALTER TABLE statement for the target table of a prepared positioned update invalidates the prepared positioned update statement.

Both searched and positioned update statements depend on all aliases used in the query. Dropping an alias invalidates a prepared update statement if the latter statement uses the alias.

Dropping or adding triggers on the target table of the update invalidates the update statement.

Interaction with Java Data Types

The values in columns that store Java data types are altered only when they are the target columns of an UPDATE statement. Altering the object itself by calling a Java method does not write that change to the database.