MySQL multi-table update SQL statement Summary

MySQL Multi-table update has several different formulations.

Suppose we have two tables, one table for the product table, a product listing price, and another table for the Productprice table, which we want to update the Price field in the Productprice table to 80% of the price field in the prices table.

In MySQL we have several means to do this, one is update table1 t1, table2 ts ... The way:

UPDATE Product P,Productprice Ppset pp.Price=Pp.Price* 0.8where p. productid = Pp. productidand p. datecreated <  ' 2004-01-01 '            

Another way is to use inner join and then update:

UPDATE product PINNER JOIN productprice ppon p.ProductId=Pp.Productidset pp.Price= Pp. price * 0.8where p. datecreated <  ' 2004-01-01 '            

Alternatively, we can use the left outer join to do a multi-table update, 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:

UPDATE product Pleft JOIN productprice ppon p.ProductId= Pp. productidset p. deleted = 1where pp. productid is null            

In addition, the above examples are related to the two tables, but only update the records in a table, in fact, you can update both tables, the following SQL:

UPDATE product PINNER JOIN productprice ppon p.ProductId=Pp.Productidset pp.Price=Pp.Price* 0.8,p. dateupdate = Curdate () where p. datecreated <  ' 2004-01-01 '            

Two tables are associated, updating the Price field of the Productprice table and the Dateupdate two fields of the Product table field.

MySQL multi-table update SQL statement Summary