UPDATE - Single Table

This page of our MySQL documentation relates to the MySQL statement, UPDATE, in particular, updating a single table, as opposed to updating multiple tables.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

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

Explanation

This syntax of the MySQL statement, UPDATE 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 equal sign. The value can be a static value or an expression. If a column in a MySQL 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.

Examples

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 MySQL 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; otherwise, 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 non-duplicate rows.

The results of such a MySQL 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 were it not for the duplication problem.

An example follows 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 - Limiting results for details about the LIMIT clause and SELECT - Ordering results for details about the ORDER BY clause. An example follows 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 effect the results of those that follow within the same statement.