MySQL multi-table update SQL statement Summary

MySQL cross-table update has always been a topic of concern to everyone, this article describes the MySQL Multi-table update in practice several different ways of writing

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:

UPDATESET=*0.8WHERE= and < ' 2004-01-01 '

Another way is to use inner join and then update:

UPDATEINNERJOINon=SET= * 0.8 WHERE < ' 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:

UPDATEleftJOIN on = SET = 1 WHERE  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 p  inner  join   Productprice pp  on  p.productid  Pp.productid  Span style= "color: #0000ff;" >set  pp.price =  pp.price *  0.8   =   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