UPDATE Statement

statement_UPDATE ::= UPDATE [scheme.]table_name SET column_name = expression 
    {, column_name = expression }… [WHERE condition]

Description

The UPDATE statement (or Searched UPDATE) allows you to update column values in a table. Each specified column is assigned a new value specified in the expression when this statement is executed. This statement affects each record that fulfills the condition, or all table records, if the condition is not set. The number of affected records may be retrieved using the system variable @@ROWCOUNT.

If the table is prefixed with an application name (on the same server), the table will be searched for in this application (schema).

You can use an editable query (VIEW) instead of a table in this statement (this is recognized upon compilation). It is not checked whether the updated values fulfill the query condition.

The UPDATE statement triggers the UPDATE trigger for each updated record. BEFORE UPDATE trigger is executed BEFORE the actual change, AFTER UPDATE trigger AFTER the change. If an error occurs inside the trigger, the UPDATE statement is rolled back.

If the table specified in the UPDATE statement is a parent table in the active referential integrity (RI), two cases may occur:

If the ON UPDATE CASCADE, SET NULL or SET DEFAULT keywords are specified in the RI definition, the action specified in the definition will be executed with the records in the child table that depend on the updated record.
Otherwise an error 40006 (REFERENTIAL_CONSTRAIN) occurs, which causes a default rollback.

If the table specified in the UPDATE statement is an child table in the RI and the new values break the RI rule, an error 40006 (REFERENTIAL_CONSTRAINT) occurs.

The user who executes this statement must have sufficient effective privileges for updating table records.

Usage Example:

Raise the salary by 10% for all employees in the department:

UPDATE Employed
SET salary = salary*1.1
WHERE department = 1

The table PriceList contains the columns "id" and "price", the table Catalogue contains the columns "id" and "new_price". Change the values of the price column in the PriceList table according to the values in the Catalogue table:

UPDATE PriceList
 SET price = (SELECT new_price
             FROM Catalogue
             WHERE Catalogue.id=PriceList.id)
 WHERE EXISTS (SELECT new_price
               FROM Catalogue
                WHERE Catalogue.id=PriceList.id);

See

REVOKE Statement

SQL Statements

INSERT Statement