Cross Table Update with MySQL

Using MySQL version 4.0 or higher you can update a table by joining two or more tables together; note that the examples shown in this article are not possible with MySQL 3.23 or earlier. By joining two tables together you can update one table based on fields in associated records in another table.

Let's say for example you have a product table which stores information about products and a productPrice table which has pricing information and you want to update the prices based on when the product was created (in the examples below you want to discount all your older stuff to 80% of the current price).

In MySQL you can do this in one of two ways. The first is do do a join using commas, like so:

UPDATE product p, productPrice pp
SET pp.price = pp.price * 0.8
WHERE p.productId = pp.productId
AND p.dateCreated < '2004-01-01'

The second way is to use inner join syntax as shown below. This syntax is slightly more flexible as it means you can use left and right joins as well as inner joins, whereas the previous example is an implicit inner join.

UPDATE product p
INNER JOIN productPrice pp
ON p.productId = pp.productId
SET pp.price = pp.price * 0.8
WHERE p.dateCreated < '2004-01-01'

Another example of doing this in MySQL but using a left join to flag all the products that do not have associated entries in the productPrice table is as follows:

UPDATE product p
LEFT JOIN productPrice pp
ON p.productId = pp.productId
SET p.deleted = 1
WHERE pp.productId IS null

This isn't necessarily a very realistic example as you would be more likely to want to straight out delete the non-matching entries or use a select query instead to display them in a list for editing but it illustrates how it can be done. The article about how to delete records with MySQL using a cross join uses these examples to delete instead of update.

Note that it's also possible in MySQL to update values in all the tables that are joined in the query. In this example we're doing the same query as in the first two examples but also updating the "dateUpdated" value in the product table.

UPDATE product p
INNER JOIN productPrice pp
ON p.productId = pp.productId
SET pp.price = pp.price * 0.8,
p.dateUpdate = CURDATE()
WHERE p.dateCreated < '2004-01-01'

(CURDATE() is the MySQL function to return the current system date. For other date and time functions in MySQL refer to the MySQL date and time functions manual page).