MySQL Cookbook

Updating One Table Based on Values in Another

Problem

You need to update existing records in one table based on the contents of records in another table, but MySQL doesn’t yet allow join syntax in the WHERE clause of UPDATE statements. So you have no way to associate the two tables.

Solution

Create a new table that is populated from the result of a join between the original table and the table containing the new information. Then replace the original table with the new one. Or write a program that selects information from the related table and issues the queries necessary to update the original table. Or use mysql to generate and execute the queries.

Discussion

Sometimes when updating records in one table, it’s necessary to refer to records in another table. Recall that the states table used in several earlier recipes contains rows that look like this:

mysql> SELECT * FROM states;
+----------------+--------+------------+----------+
| name           | abbrev | statehood  | pop      |
+----------------+--------+------------+----------+
| Alaska         | AK     | 1959-01-03 |   550043 |
| Alabama        | AL     | 1819-12-14 |  4040587 |
| Arkansas       | AR     | 1836-06-15 |  2350725 |
| Arizona        | AZ     | 1912-02-14 |  3665228 |
...

Now suppose that you want to add some new columns to this table, using information from another table, city, that contains information about each state’s capital city and largest (most populous) city:

mysql> SELECT * FROM city; +----------------+----------------+----------------+ | state | capital | largest | ...