|< Day Day Up >|
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 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.
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.
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
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;
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."
|< Day Day Up >|