Single-table syntax:
UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
Multiple-table syntax:
UPDATE [LOW_PRIORITY] [IGNORE] table_references
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_condition]
For the single-table syntax, the UPDATE
statement updates columns of existing rows in
tbl_name with new values. The
SET clause indicates which columns to modify
and the values they should be given. 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. It is specified as
described in Section 13.2.7, “SELECT Syntax”.
The UPDATE statement supports the following
modifiers:
If you use the LOW_PRIORITY keyword,
execution of the UPDATE is delayed until
no other clients are reading from the table.
If you use 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 closet
valid values instead.
If you access a column from tbl_name
in an expression, UPDATE uses the current
value of the column. For example, the following statement sets
the age column to one more than its current
value:
UPDATE persondata SET age=age+1;
UPDATE assignments are evaluated from left to
right. For example, the following statement doubles the
age column, and then increments it:
UPDATE persondata SET age=age*2, age=age+1;
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, the column
is set to the default value appropriate for the data type and
the warning count is incremented. The default value is
0 for numeric types, the empty string
('') for string types, and the
“zero” value for date and time types.
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.
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.7.1, “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 allowed in
SELECT statements, such as LEFT
JOIN.
You need the UPDATE privilege only for
columns referenced in a multiple-table UPDATE
that are actually updated. You need only the
SELECT privilege for any columns that are
read but not modified.
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.6.4, “FOREIGN KEY Constraints”.
Currently, you cannot update a table and select from the same
table in a subquery.
