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

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

MySQL Transactional and Locking Statements
1110
and the warning count is incremented. The implicit default value is
0
for numeric types, the empty string
(
''
) for string types, and the “zero” value for date and time types. See Section 11.1.7, “Data Type
Default Values” .
UPDATE
returns the number of rows that were actually changed. The
mysql_info()
C API function
returns the number of rows that were matched and updated and the number of warnings that occurred
during the
UPDATE
.
You can use
LIMIT row_count
to restrict the scope of the
UPDATE
. A
LIMIT
clause is a rows-
matched restriction. The statement stops as soon as it has found
row_count
rows that satisfy the
WHERE
clause, whether or not they actually were changed.
If an
UPDATE
statement includes an
ORDER BY
clause, the rows are updated in the order specified
by the clause. This can be useful in certain situations that might otherwise result in an error. Suppose
that a table
t
contains a column
id
that has a unique index. The following statement could fail with a
duplicate-key error, depending on the order in which rows are updated:
UPDATE t SET id = id + 1;
For example, if the table contains 1 and 2 in the
id
column and 1 is updated to 2 before 2 is updated
to 3, an error occurs. To avoid this problem, add an
ORDER BY
clause to cause the rows with larger
id
values to be updated before those with smaller values:
UPDATE t SET id = id + 1 ORDER BY id DESC;
You can also perform
UPDATE
operations covering multiple tables. However, you cannot use
ORDER
BY
or
LIMIT
with a multiple-table
UPDATE
. The
table_references
clause lists the tables involved in
the join. Its syntax is described in Section 13.2.8.2, “
JOIN
Syntax” . Here is an example:
UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;
The preceding example shows an inner join that uses the comma operator, but multiple-table
UPDATE
statements can use any type of join permitted in
SELECT
statements, such as
LEFT JOIN
.
If you use a multiple-table
UPDATE
statement involving
InnoDB
tables for which there are foreign key
constraints, the MySQL optimizer might process tables in an order that differs from that of their parent/
child relationship. In this case, the statement fails and rolls back. Instead, update a single table and
rely on the
ON UPDATE
capabilities that
InnoDB
provides to cause the other tables to be modified
accordingly. See Section 14.2.3.4, “
InnoDB
and
FOREIGN KEY
Constraints” .
Currently, you cannot update a table and select from the same table in a subquery.
Index hints (see Section 13.2.8.3, “Index Hint Syntax” ) are accepted but ignored for
UPDATE
statements.
13.3. MySQL Transactional and Locking Statements
MySQL supports local transactions (within a given client session) through statements such as
SET autocommit
,
START TRANSACTION
,
COMMIT
, and
ROLLBACK
. See Section 13.3.1, “
START
TRANSACTION
,
COMMIT
, and
ROLLBACK
Syntax” . Beginning with MySQL 5.0, XA transaction support
is available, which enables MySQL to participate in distributed transactions as well. See Section 13.3.7,
“XA Transactions” .
13.3.1.
START TRANSACTION
,
COMMIT
, and
ROLLBACK
Syntax
START TRANSACTION [WITH CONSISTENT SNAPSHOT]
BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET autocommit = {0 | 1}