Mysql cross-Table update

Mysql cross-Table update assumes that we have two tables. One Table stores Product information for the Product table, with the Product Price column Price. The other table is the ProductPrice table, update the Price field in the ProductPrice table to 80% of the Price field in the Price table. In Mysql, we have several ways to do this. One is to update table1 t1, table2 ts...: the code is as follows:

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

 

Another method is to use inner join and then update it: the code is as follows:

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' 

 

In addition, we can also use left outer join to update multiple tables. For example, if there is no Product price record in the ProductPrice table, set the isDeleted field of the Product table to 1. The following SQL statement: the code is as follows:

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

 

In addition, the above examples are associations between two tables, but only the records in one table can be updated at the same time. The following SQL code:

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' 

 

The two tables are joined to update the price field of the ProductPrice table and the dateUpdate field of the Product table.