Table of Contents for

MySQL in a Nutshell, 2nd Edition

This statement changes existing rows of data in a table. The first syntax shown updates only one table per statement. The second syntax can be used to update or reference data in multiple tables from one statement. Explanations of both types of statements and examples of their use follow.

Single table UPDATE

UPDATE [LOW_PRIORITY] [IGNORE] table 
SET column=expression[, ...] 
[WHERE condition] 
[ORDER BY {column|expression|position} [ASC|DESC], ...] 
[LIMIT {[offset,] count|count OFFSET offset}]

This syntax changes a single table. The SET clause specifies each column that should change and the value to which it is to be set, separated by an equals sign. The value can be a static value or an expression. If a column in a table is defined as NOT NULL, and if an UPDATE statement then sets its value to NULL, the default value for the column will be used if it is available; otherwise, an error is generated.

The LOW_PRIORITY keyword may be used to instruct the server to wait until all other queries related to the table in which data is to be added are completed before running the UPDATE statement. When the table is free, it will be locked for the UPDATE statement and thereby prevent concurrent data updates or inserts.

Normally, if one of the updates would create a duplicate row (a row that shares the same value as an existing row in a column declared to be unique), the statement reports an error. The statement is then terminated and no more rows are updated. If the table is InnoDB, BDB, or NDB, the entire transaction is reversed or rolled back; if not, the rows that were updated before the error will remain updated. However, if the IGNORE keyword is used, the server ignores any errors encountered, suppresses error messages, and continues updating nonduplicate rows.

The results of such a statement will display like this:

Query OK, 120 rows affected (4.20 sec)
Records: 125 Duplicates: 5 Warnings: 0

Notice that only 120 rows were updated, although 125 would have been updated if there had been no duplication problem.

Here is an example of the UPDATE statement using this syntax:

UPDATE clients
SET client_name = 'Geoffrey & Company', 
city = 'Boston', state = 'MA' 
WHERE client_name LIKE 'Geoffrey%';

This example sets the values of two columns for any rows (probably only one in this case) that meet the condition of the WHERE clause using the LIKE operator. Only these two columns will be updated in the matching rows. If there are several rows with the column client_name containing a starting value of Geoffrey, all of them will be changed.

The number of rows that are updated can be limited by using the LIMIT clause. As of version 4.0.13 of MySQL, the LIMIT clause is based on the number of rows matched, not necessarily the number changed. Starting with version 4.0.0 of MySQL, you can also choose to UPDATE only the first few rows found in a certain order by using the ORDER BY clause. See the SELECT statement earlier in this chapter for details about the ORDER BY and the LIMIT clauses. Here is an example of an UPDATE statement using both of these clauses:

UPDATE clients
SET client_terms = client_terms + 15
WHERE client_city = 'Boston'
AND YEAR(date_opened) < 2005
ORDER BY date_opened
LIMIT 50;

This example indicates that we’ve decided to somewhat arbitrarily upgrade the client terms (i.e., allow 15 additional days to pay their invoices) for any clients located in Boston who opened an account before the year 2005, but only for the first 50 clients based on the date order in which their account was opened. Notice that the value of the column client_terms is set with an expression that refers to the value of the column before the UPDATE statement is executed. Expressions are calculated from left to right, so the results of one expression could affect the results of those that follow within the same statement.

Multiple table UPDATE

UPDATE [LOW_PRIORITY] [IGNORE] table_reference 
SET column=expression[, ...] 
[WHERE condition]

This syntax of the UPDATE statement, available as of version 4.0.4 of MySQL, allows for multiple tables to be updated or referenced in one SQL statement. A SET clause specifies each column that should change and the value to which it is to be set, separated by an equals sign. The value can be a static value or an expression. The keywords LOW_PRIORITY and IGNORE are handled the same way as in the first syntax for the UPDATE statement. The ORDER BY and the LIMIT clauses are not available with the multiple-table syntax.

The columns by which tables are joined may be given in the WHERE clause (e.g., WHERE clients.branch_id=branches.client_id), or with the JOIN clause.

Here is an example using the JOIN clause:

UPDATE clients JOIN branches USING (branch_id) 
SET client_terms = client_terms + 60 
WHERE location = 'New Orleans';

In this example, only one table is being changed, but two are joined to determine which clients belong to the New Orleans branch in order to be able to give them 60 additional days to pay their bills due to a recent hurricane. See the JOIN clause earlier in this chapter for details on joining tables.