Oracle 5.0, Справочное руководство

Страница: 1129 / 3232

UPDATE
Syntax
1109
Single-table syntax:
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET col_name1 ={ expr1 |DEFAULT} [, col_name2 ={ expr2 |DEFAULT}] ...
[WHERE where_condition ]
[ORDER BY ...]
[LIMIT row_count ]
Multiple-table syntax:
UPDATE [LOW_PRIORITY] [IGNORE] table_references
SET col_name1 ={ expr1 |DEFAULT} [, col_name2 ={ expr2 |DEFAULT}] ...
[WHERE where_condition ]
For the single-table syntax, the
UPDATE
statement updates columns of existing rows in the named
table with new values. The
SET
clause indicates which columns to modify and the values they should
be given. Each value can be given as an expression, or the keyword
DEFAULT
to set a column
explicitly to its default value. The
WHERE
clause, if given, specifies the conditions that identify which
rows to update. With no
WHERE
clause, all rows are updated. If the
ORDER BY
clause is specified, the
rows are updated in the order that is specified. The
LIMIT
clause places a limit on the number of rows
that can be updated.
For the multiple-table syntax,
UPDATE
updates rows in each table named in
table_references
that
satisfy the conditions. In this case,
ORDER BY
and
LIMIT
cannot be used.
where_condition
is an expression that evaluates to true for each row to be updated. For expression
syntax, see Section 9.5, “Expression Syntax” .
table_references
and
where_condition
are is specified as described in Section 13.2.8,
“
SELECT
Syntax” .
You need the
UPDATE
[578] privilege only for columns referenced in an
UPDATE
that are actually
updated. You need only the
SELECT
[578] privilege for any columns that are read but not modified.
The
UPDATE
statement supports the following modifiers:
• With the
LOW_PRIORITY
keyword, execution of the
UPDATE
is delayed until no other clients are
reading from the table. This affects only storage engines that use only table-level locking (such as
MyISAM
,
MEMORY
, and
MERGE
).
• With the
IGNORE
keyword, the update statement does not abort even if errors occur during the
update. Rows for which duplicate-key conflicts occur are not updated. Rows for which columns are
updated to values that would cause data conversion errors are updated to the closest valid values
instead.
If you access a column from the table to be updated in an expression,
UPDATE
uses the current value
of the column. For example, the following statement sets
col1
to one more than its current value:
UPDATE t1 SET col1 = col1 + 1;
The second assignment in the following statement sets
col2
to the current (updated)
col1
value, not
the original
col1
value. The result is that
col1
and
col2
have the same value. This behavior differs
from standard SQL.
UPDATE t1 SET col1 = col1 + 1, col2 = col1;
Single-table
UPDATE
assignments are generally evaluated from left to right. For multiple-table updates,
there is no guarantee that assignments are carried out in any particular order.
If you set a column to the value it currently has, MySQL notices this and does not update it.
If you update a column that has been declared
NOT NULL
by setting to
NULL
, an error occurs if strict
SQL mode is enabled; otherwise, the column is set to the implicit default value for the column data type