UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_definition]
[ORDER BY ...]
[LIMIT row_count]

or:

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name [, tbl_name ...]
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_definition]

UPDATE updates columns in existing table rows with new values. The SET clause indicates which columns to modify and the values they should be given. The WHERE clause, if given, specifies which rows should be updated. Otherwise, all rows are updated. If the ORDER BY clause is specified, the rows will be updated in the order that is specified.

If you specify the keyword LOW_PRIORITY, execution of the UPDATE is delayed until no other clients are reading from the table.

If you specify the keyword IGNORE, the update statement will not abort even if duplicate key errors occur during the update. Rows that would cause conflicts will not be updated.

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:

mysql> UPDATE persondata SET age=age+1;

UPDATE assignments are evaluated from left to right. For example, the following statement doubles the age column, then increments it:

mysql> UPDATE persondata SET age=age*2, age=age+1;

If you set a column to the value it currently has, MySQL notices this and doesn't update it.

UPDATE returns the number of rows that were actually changed. In MySQL Version 3.22 or later, the C API function mysql_info() returns the number of rows that were matched and updated and the number of warnings that occurred during the UPDATE. 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 column type and the warning count is incremented. The default value is is 0 for numeric types, the empty string ('') for string types, and the ``zero'' value for date and time types.

Starting from MySQL version 3.23, you can use LIMIT row_count to restrict the scope of the UPDATE. A LIMIT clause works as follows:

* Before MySQL 4.0.13, LIMIT is a rows-affected restriction. The statement stops as soon as it has changed row_count rows that satisfy the WHERE clause.
* From 4.0.13 on, LIMIT 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 ORDER BY clause is used (available from MySQL 4.0.0), the rows will be updated in that order. This is really useful only in conjunction with LIMIT.

Starting with MySQL Version 4.0.4, you can also perform UPDATE operations that cover multiple tables:

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

The example shows an inner join using the comma operator, but multiple-table UPDATE statements can use any type of join allowed in SELECT statements, such as LEFT JOIN.

Note: You cannot use ORDER BY or LIMIT with multiple-table UPDATE.

Before MySQL 4.0.18 one needed the UPDATE privilege for all tables used in a multi table UPDATE (even if they where not updated). In MySQL 4.0.18 one need the SELECT privilege for any columns that are only read.

back to MySQL reference