<< TRUNCATE TABLE statement | PRIMARY KEY >>

Statement dependency system

<< TRUNCATE TABLE statement | PRIMARY KEY >>

Derby Reference Manual

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

SQL clauses

CONSTRAINT clause

A CONSTRAINT clause is an optional part of a

CREATE TABLE statement

ALTER

TABLE statement

. A constraint is a rule to which data must conform. Constraint names

are optional.

A CONSTRAINT can be one of the following:

· a column-level constraint

Column-level constraints refer to a single column in the table and do not specify a
column name (except check constraints). They refer to the column that they follow.

· a table-level constraint