Syntax
{
UPDATE table-Name [[AS] correlation-Name]
SET column-Name = Value
[ , column-Name = Value} ]*
[WHERE clause] |
UPDATE table-Name
SET column-Name = Value
[ , column-Name = Value ]*
WHERE CURRENT OF
}
where Value is defined as follows:
Expression | DEFAULT
The first syntactical form, called a searched update, updates the value of
one or more columns for all rows of the table for which the WHERE clause evaluates to TRUE.
The second syntactical form, called a positioned update, updates one or more columns on the
current row of an open, updatable cursor. If columns were specified in the
FOR UPDATE clause of the SELECT
statement used to generate the cursor, only those columns can be updated. If no columns were specified
or the select statement did not include a FOR UPDATE clause, all columns may be updated.
Specifying
DEFAULT for the update value sets the value of the column to the default defined
for that table.
The DEFAULT literal is the only value which you can directly assign to a
generated column. Whenever you alter the value of a column referenced by the
generation-clause of a generated column,
Derby recalculates the value
of the generated column.
Example
-- All the employees except the manager of
-- department (WORKDEPT) 'E21' have been temporarily reassigned.
-- Indicate this by changing their job (JOB) to NULL and their pay
-- (SALARY, BONUS, COMM) values to zero in the EMPLOYEE table.
UPDATE EMPLOYEE
SET JOB=NULL, SALARY=0, BONUS=0, COMM=0
WHERE WORKDEPT = 'E21' AND JOB <> 'MANAGER'
-- PROMOTE the job (JOB) of employees without a specific job title to MANAGER
UPDATE EMPLOYEE
SET JOB = 'MANAGER'
WHERE JOB IS NULL;
// Increase the project staffing (PRSTAFF) by 1.5 for all projects
stmt.executeUpdate("UPDATE PROJECT SET PRSTAFF = "
"PRSTAFF + 1.5" +
"WHERE CURRENT OF" + ResultSet.getCursorName());
-- Change the job (JOB) of employee number (EMPNO) '000290' in the EMPLOYEE table
-- to its DEFAULT value which is NULL
UPDATE EMPLOYEE
SET JOB = DEFAULT
WHERE EMPNO = '000290'
Statement 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 has not
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.
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.
