The UPDATE statement modifies the contents of existing records. To use it, name the table you want to update, provide a SET clause that lists one or more column value assignments, and optionally specify a WHERE clause that identifies which records to update:

Single-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
    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 example, to set the age column to 30 for the people table record that has an id value of 12, use this statement:

UPDATE people SET age = 30 WHERE id = 12;

To update multiple columns, separate the column value assignments in the SET clause by commas:

UPDATE people SET age = 30, name = 'Wilhelm' WHERE id = 12;

The WHERE clause specifies the conditions that records must satisfy to be selected for updating. If you omit the WHERE clause, MySQL updates every row in the table.

The effects of column assignments made by an UPDATE are subject to column type constraints, just as they are for an INSERT or REPLACE. By default, if you attempt to update a column to a value that doesn't match the column definition, MySQL converts or truncates the value. If you enable strict SQL mode, the server will be more restrictive about allowing invalid values.

It's possible for an UPDATE statement to have no effect. This can occur under the following conditions:

• When the statement matches no records for updating. This always occurs if the table is empty, of course. It might also occur if no records match the conditions specified in the WHERE clause.
• When the statement does not actually change any column values. For example, if you set a date-valued column to '2000-01-01' and the column already has that date as its value, MySQL ignores the assignment.

UPDATE reports a rows-affected count to indicate how many rows actually were changed. This count doesn't include rows that were selected for updating but for which the update didn't change any columns from their current values. The following statement produces a row count of zero because it doesn't actually change any values, even if there is a record with an id value of 12:

UPDATE people SET age = age WHERE id = 12;

If a table contains a TIMESTAMP column that has ON UPDATE CURRENT_TIMESTAMP in its definition, that column is updated automatically only if another column changes value. An UPDATE that sets columns to their current values does not change the TIMESTAMP. If you need the TIMESTAMP to be updated for every UPDATE, you can set it explicitly to the value of the CURRENT_TIMESTAMP function.

With respect to handling of records with unique key values, UPDATE is similar to REPLACE in some ways, but the two aren't equivalent:

• UPDATE does nothing if there's no existing record in the table that contains the specified key values. REPLACE doesn't require an existing record with the key values and adds one if none exists.
• UPDATE can be used to change some columns in an existing record while leaving others unchanged. REPLACE entirely discards the existing record. To achieve the effect of leaving some columns unchanged with REPLACE, the new record must specify the same values in those columns that the existing record has. (Another way to update only some columns for an insert operation is to use INSERT with the ON DUPLICATE KEY UPDATE clause.)

UPDATE with ORDER BY and LIMIT

UPDATE by default makes no guarantee about the order in which rows are updated. This can sometimes result in problems. Suppose that the people table contains two rows, where id is a PRIMARY KEY:

mysql> SELECT * FROM people;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  2 | Victor |   21 |
|  3 | Susan  |   15 |
+----+--------+------+

If you want to renumber the id values to begin at 1, you might issue this UPDATE statement:

UPDATE people SET id = id - 1;

The statement succeeds if it updates id values first by setting 2 to 1 and then 3 to 2. However, it fails if it first tries to set 3 to 2. That would result in two records having an id value of 2, so a duplicate-key violation occurs. To solve this problem, add an ORDER BY clause to cause the row updates to occur in a particular order:

UPDATE people SET id = id - 1 ORDER BY id;

UPDATE also allows a LIMIT clause, which places a limit on the number of records updated. For example, if you have two identical people records with a name value of 'Nicolas' and you want to change just one of them to 'Nick', use this statement:

UPDATE people SET name = 'Nick' WHERE name = 'Nicolas' LIMIT 1;

ORDER BY and LIMIT may be used together in the same UPDATE statement.

Preventing Dangerous UPDATE Statements

As mentioned earlier, an UPDATE statement that includes no WHERE clause updates every row in the table. Normally, this isn't what you want. It's much more common to update only a specific record or small set of records. An UPDATE with no WHERE is likely to be accidental, and the results can be catastrophic.

It's possible to prevent UPDATE statements from executing unless the records to be updated are identified by key values or a LIMIT clause is present. This might be helpful in preventing accidental overly broad table updates. The mysql client supports this feature if you invoke it with the --safe-updates option.

Multiple-Table UPDATE Statements

MySQL allows the use of join syntax in UPDATE and DELETE statements to enable updates or deletes that involve multiple tables. Such statements can be used to perform the following operations:

• Update rows in one table by transferring information from another table
• Update rows in one table, determining which rows to update by referring to another table
• Update rows in multiple tables with a single statement

A multiple-table UPDATE is an extension of a single-table statement:

• Following the UPDATE keyword, name the tables involved in the operation, separated by commas. (You must name all the tables used in the query, even if you aren't updating all of them.)
• In the WHERE clause, describe the conditions that determine how to match records in the tables.
• In the SET clause, assign values to the columns to be updated. These assignments can refer to columns from any of the joined tables.

For example, this statement identifies matching records in two tables based on id values, and then copies the name column from t2 to t1:

UPDATE t1, t2 SET t1.name = t2.name WHERE t1.id = t2.id;

The ORDER BY and LIMIT clauses normally supported by UPDATE aren't allowed when these statements are used for multiple-table operations.

Update Statement Related FAQs