7.2 The UPDATE Statement

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:

UPDATE table_name SET column_name1 = value1, column_name2 = value2, ...

WHERE ... ;

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. If you attempt to update a column to a value that doesn't match the column definition, MySQL converts the value. Values that lie outside the range of a numeric column are converted to the nearest in-range value. String values that are too long for a string column are truncated to fit. Updating a NOT NULL column to NULL sets it to the default value for the column type: 0 for numeric columns, the empty string for string columns, and the "zero" temporal value for temporal columns.

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 second of the following statements produces a row count of zero because it doesn't actually change any values:

mysql> UPDATE people SET age = age + 1 WHERE id = 12;

Query OK, 1 row affected (0.02 sec)

mysql> UPDATE people SET age = age WHERE id = 12;

Query OK, 0 rows affected (0.00 sec)

Note that if a table contains a TIMESTAMP column, 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 NOW() function.

Some clients or APIs enable you to ask MySQL to return a rows-matched count rather than a rows-affected count. This causes the row count to include all rows selected for updating, even if their columns weren't changed from their present values. The C API provides an option for selecting the type of count you want. The MySQL Connector/J Java driver tells MySQL to operate in rows-matched mode by default because that behavior is mandated by the JDBC specification.

UPDATE is similar to REPLACE in some respects, but the two aren't equivalent:

• UPDATE does nothing if there's no existing record containing the specified key values in the table. 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.

7.2.1 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. See section 3.2.8, "Using the --safe-updates Option," for more information.

7.2.2 Using 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.

7.2.3 Multiple-Table UPDATE Statements

UPDATE supports a multiple-table syntax that enables you to update a table using the contents of another table. This syntax also allows multiple tables to be updated simultaneously. The syntax has much in common with that used for writing multiple-table SELECT statements, so it's discussed in Chapter 8, "Joins."