11.4. The UPDATE Statement


11.4. 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_name = value [, column_name = value] ...     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. 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. See Section 5.8, "Handling Missing or Invalid Data 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:

 mysql> UPDATE people SET age = age WHERE id = 12; Query OK, 0 rows affected (0.00 sec) 

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.

Some client programs or APIs enable you to ask the MySQL server 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 when you establish a connection to the server. The MySQL Connector/J Java driver tells the server to operate in rows-matched mode because that behavior is mandated by the JDBC specification.

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.)

11.4.1. 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.

11.4.2. 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 2.9, "Using the --safe-updates Option," for more information.

11.4.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 Section 12.5, "Multiple-Table UPDATE and DELETE Statements."



MySQL 5 Certification Study Guide
MySQL 5.0 Certification Study Guide
ISBN: 0672328127
EAN: 2147483647
Year: 2006
Pages: 312

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net