[php] MySQL 兩個資料表 UPDATE

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 assocated 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'

http://www.electrictoolbox.com/article/mysql/cross-table-update/