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