MySQueaL Resources

UPDATE - Multiple Tables Statement

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.

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.

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.