UPDATE - Multiple Tables

This syntax of the MySQL statement, UPDATE, available as of version 4.0.4 of MySQL, allows for multiple MySQL tables to be updated or referenced in one MySQL statement.

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_reference 
SET column=expression[, ...] 
[WHERE condition]

Explanation

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

Examples

An example follows 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 bill due to a recent hurricane. See the JOIN clause for details on joining tables.