# UPDATE statement

#### Syntax

    {
        UPDATE table-Name
            SET column-Name = Value
            [ , column-Name = Value} ]*
            [WHERE clause] |
        UPDATE table-Name
            SET column-Name = Value
            [ , column-Name = Value ]*
            WHERE CURRENT OF
    }

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.

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

- Value

Parent topic: Statements
Related concepts
Interaction with the dependency system
CREATE statements
DROP Statements
RENAME statements
SET statements
Related reference
ALTER TABLE statement
GRANT statement
REVOKE statement
CALL (PROCEDURE)
CONSTRAINT clause
DECLARE GLOBAL TEMPORARY TABLE statement
DELETE statement
FOR UPDATE clause
FROM clause
GROUP BY clause
HAVING clause
INNER JOIN
INSERT statement
JOIN operation
LEFT OUTER JOIN
LOCK TABLE statement
ORDER BY clause
Query
RIGHT OUTER JOIN
ScalarSubquery
SelectExpression
SELECT statement
TableExpression
TableSubquery
VALUES expression
WHERE clause
WHERE CURRENT OF clause