Data changes using UPDATE

The UPDATE statement specifies the row or rows you want changed, and the expressions to be used as the new values for specific
columns in those rows.

You can use the UPDATE statement to change single rows, groups of rows, or all the rows in a table. Unlike the other data
manipulation statements (INSERT, MERGE, and DELETE), the UPDATE statement can also modify rows in more than one table at the
same time. In all cases, the execution of the UPDATE statement is atomic; either all of the rows are modified without error,
or none of them are. For example, if one of the values being modified is the wrong data type, or if the new value causes a
CHECK constraint violation, the UPDATE fails and the entire operation is rolled back.

The search condition need not refer to the column being updated. The company ID for Newton Entertainments is 109. As the ID
value is the primary key for the table, you could be sure of updating the correct row using the following statement:

UPDATE Customers
SET CompanyName = 'Einstein, Inc.'
WHERE ID = 109;

Tip

The SET clause specifies which columns are to be updated, and what their new values are. The WHERE clause determines the row
or rows to be updated. If you do not have a WHERE clause, the specified columns of all rows are updated with the values given
in the SET clause.

The expressions specified in a SET clause can be a constant literal, a host or SQL variable, a subquery, a special value such
as CURRENT TIMESTAMP, an expression value pulled from another table, or any combination of these. You can also specify DEFAULT
in a SET clause to denote the default value for that base table column. If the data type of the expression differs from the
data type of the column to be modified, the database server automatically converts the expression to the column's type, if
possible. If the conversion is not possible, a data exception results and the UPDATE statement fails.

You can use the SET clause to set the value of a variable, in addition to modifying column values. This example assigns a
value to the variable @var in addition to updating table T:

UPDATE T
SET @var = expression1, col1 = expression2
WHERE...;

This is roughly equivalent to the serial execution of a SELECT statement, followed by an UPDATE:

The advantage of variable assignment within an UPDATE statement is that the variable's value can be set within the execution
of the statement while write locks are held, which prevents the assignment of unexpected values due to concurrent update activity
from other connections.

The WHERE clause specifies which rows are to be updated by applying search-condition to the table or Cartesian product of table expressions specified in the UPDATE statement. For example, the following statement
replaces the One Size Fits All Tee Shirt with an Extra Large Tee Shirt:

The semantics of this form of the UPDATE statement are to first compute a result set consisting of all combinations of rows
from each table-expression, subsequently apply the search-condition in the WHERE clause, and then order the resulting rows using the ORDER BY clause. This computation results in the set of
rows that will be modified. Each table-expression can consist of joins of base tables, views, and derived tables. The syntax permits the update of one or more tables with
values from columns in other tables. The query optimizer may reorder the operations to create a more efficient execution strategy
for the UPDATE statement.

If a base table row appears in a set of rows to be modified more than once, then the row is updated multiple times if the
row's new values differ with each manipulation attempt. If a BEFORE ROW UPDATE trigger exists, the BEFORE ROW UPDATE trigger
is fired for each individual row manipulation, subject to the trigger's UPDATE OF column-list clause. AFTER ROW UPDATE triggers are also fired with each row manipulation, but only if the row's values are actually changed,
subject to the trigger's UPDATE OF column-list clause.

Triggers are fired for each updated table based on the type of the trigger and the value of the ORDER clause with each trigger
definition. If an UPDATE statement modifies more than one table, however, the order in which the tables are updated is not
guaranteed.

The following example creates a BEFORE ROW UPDATE trigger and an AFTER STATEMENT UPDATE trigger on the Products table, each
of which prints a message in the database server messages window:

Suppose you then execute an UPDATE statement over a join of the Products table with the SalesOrderItems table, to discount
by 5% those products that have shipped since April 1, 2001 and that have at least one large order:

The messages indicate that Product 700 was updated twice, as Product 700 was included in two different orders that matched
the search condition in the UPDATE statement. The duplicate updates are visible to both the BEFORE ROW trigger and the AFTER
STATEMENT trigger. With each row manipulation, the old and new values for each trigger invocation are changed accordingly. With AFTER STATEMENT triggers, the order of the rows in the temporary
tables formed by the REFERENCING clause may not match the order of the rows were modified and the precise order of those rows
is not guaranteed.

Because of the duplicate updates, Product 700's UnitPrice was discounted twice, lowering it from $15.00 initially to $13.54
(yielding a 9.75% discount), rather than only $14.25. To avoid this unintended consequence, you could instead formulate the
UPDATE statement to use an EXISTS subquery, rather than a join, to guarantee that each Product row is modified at most once.
The rewritten UPDATE statement uses both an EXISTS subquery and the alternate UPDATE statement syntax that permits a FROM
clause:

If an UPDATE statement violates a referential integrity constraint during execution, the statement's behavior is controlled
by the setting of the wait_for_commit option. If the wait_for_commit option is set to Off, and a referential constraint violation
occurs, the effects of the UPDATE statement are immediately automatically rolled back and an error message appears. If the
wait_for_commit option is set to On, any referential integrity constraint violation caused by the UPDATE statement is temporarily
ignored, to be checked when the connection performs a COMMIT.

If the base table or tables being modified have primary keys, UNIQUE constraints, or unique indexes, then row-by-row execution
of the UPDATE statement may lead to a uniqueness constraint violation. For example, you may issue an UPDATE statement that
increments all of the primary key column values for a table T:

UPDATE T SET PKcol = PKcol + 1;

When a uniqueness violation occurs during the execution of an UPDATE statement, the database server automatically:

copies the old and new values of the modified row to a temporary table with the same schema as the base table being modified.

deletes the original row from the base table. No DELETE triggers are fired as a consequence of this delete operation.

During the execution of the UPDATE statement, which rows are updated successfully and which rows are temporarily deleted depends
on the order of evaluation and cannot be guaranteed. The behavior of SQL requests from other connections executing at weaker
isolation levels (isolation levels 0, 1, or 2) may be affected by these temporarily deleted rows. Any BEFORE or AFTER ROW
triggers of the modified table are passed each row's old and new values as per the trigger's REFERENCING clause, but if the
ROW trigger issues a separate SQL statement on the modified table, rows that are held in the temporary table will be missing.

After the UPDATE statement has completed modifying each row, the rows held in the temporary table are then inserted back into
the base table. If a uniqueness violation still occurs, then the entire UPDATE statement is rolled back. Only when all of
the rows held in the temporary table have been successfully re-inserted into the base table are any AFTER STATEMENT triggers
fired.

The database server does not use a hold table to store rows temporarily if the base table being modified is the target of
a referential integrity constraint action, including ON DELETE CASCADE, ON DELETE SET NULL, ON DELETE DEFAULT, ON UPDATE CASCADE,
ON UPDATE SET NULL, and ON UPDATE DEFAULT.